SQL_FUNCTIONS= LIBNAME Statement Option

Customizes the in-memory SQL dictionary function list for this particular LIBNAME statement.

Valid in: SAS/ACCESS LIBNAME statement
Category: Data Set Control
Default: none
Restrictions: Informix and OLE DB support only SQL_FUNCTIONS=ALL.
You must specify a two-part data set name, such as <libref.member>. Otherwise, an error results.
<libref.member> must be a SAS data set. No check is performed to ensure that it is assigned to the default Base SAS engine.
Data source: Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick
Notes: Support for EXTERNAL_REPLACE and EXTERNAL_APPEND for Google BigQuery was added in the December 2025 update for SAS 9.4M8.
Support for Yellowbrick was added in SAS 9.4M7.
See: SQL_FUNCTIONS_COPY= LIBNAME option, “Passing SAS Functions” section for your specific SAS/ACCESS interface

Table of Contents

Syntax

Syntax Description

ALL

customizes the in-memory SQL dictionary function list for this particular LIBNAME statement by adding the set of all existing functions, even those that might be risky or untested.

EXTERNAL_REPLACE=<libref.member>

indicates a user-specified, external SAS data set from which the complete function list in the SQL dictionary is to be built. The assumption is that the user has already issued a LIBNAME statement to the directory where the SAS data set exists.

Restriction This value is not valid for Informix or OLE DB.

EXTERNAL_APPEND=<libref.member>

indicates a user-specified, external SAS data set from which additional functions are to be added to the existing function list in the SQL dictionary. The assumption is that the user has already issued a LIBNAME statement to the directory where the SAS data set exists.

Restriction This value is not valid for Informix or OLE DB.

Details

Using this option can cause unexpected results, especially if you use it for NULL processing and for handling date, time, and timestamp. For example, when executed without SQL_FUNCTIONS= enabled, this SAS code returns the SAS date 15308.

proc sql;
   select distinct DATE () from x.test;
quit;

However, with SQL_FUNCTIONS=ALL, the same code returns 2001-1-29, which is an ODBC-specific date format. So, be careful when you use this option.

Functions that are passed are different for each DBMS. See the DBMS-specific reference section for your SAS/ACCESS interface for list of functions that it supports.

Here are additional details to keep in mind when you add to or modify the SAS data set.

Caveats When Using the SQL_FUNCTIONS= Option

Variable

Required1

Optional2

Read-Only2

Valid Values

SASFUNCNAME

●

Truncated to 32 characters if length is greater than 32

SASFUNCNAMELEN

●

Must correctly reflect the length of SASFUNCNAME

DBMSFUNCNAME

●

Truncated to 50 characters if length is greater than 50

DBMSFUNCNAMELEN

●

Must correctly reflect the length of DBMSFUNCNAME

FUNCTION_CATEGORY

●

AGGREGATE , CONSTANT, SCALAR

FUNC_USAGE_CONTEXT

●

SELECT_LIST, WHERE_ORDERBY

FUNCTION_RETURNTYP

●

BINARY, CHAR, DATE, DATETIME, DECIMAL, GRAPHIC, INTEGER, INTERVAL, NUMERIC, TIME, VARCHAR

FUNCTION_NUM_ARGS

●

0

CONVERT_ARGS

●

Must be set to 0 for a newly added function.

ENGINEINDEX

●

Must remain unchanged for existing functions. Set to 0 for a newly added function.
1 An error results when a value is missing.
2 For new and existing functions.

Examples

Example 1: Include and Replace Existing Functions

You can use EXTERNAL_APPEND= to include one or more existing functions to the in-memory function list and EXTERNAL_REPLACE= to replace them. In this example, the DATEPART function in a SAS data set of Oracle functions by appending the function to an existing list of SAS functions.

proc sql;
create table work.append as select * 
       from work.allfuncs where sasfuncname='DATEPART';
quit;

libname mydblib oracle sql_functions="EXTERNAL_APPEND=work.append"
    sql_functions_copy=saslog;

Example 2: Replace All SAS Functions with the Oracle Equivalent

In this example, the equivalent Oracle functions in a SAS data set replace all SAS functions that contain the letter I.

proc sql;
create table work.replace as select * 
       from work.allfuncs where sasfuncname like '%I%';
quit;

libname mydblib oracle sql_functions="EXTERNAL_REPLACE=work.replace"
   sql_functions_copy=saslog;

Example 3: Add a New Function

data work.newfunc;
SASFUNCNAME = "sasname";
SASFUNCNAMELEN = 7;
DBMSFUNCNAME = "DBMSUDFName";
DBMSFUNCNAMELEN = 11;
 FUNCTION_CATEGORY = "CONSTANT";
 FUNC_USAGE_CONTEXT = "WHERE_ORDERBY";
 FUNCTION_RETURNTYP = "NUMERIC";
 FUNCTION_NUM_ARGS = 0;
 CONVERT_ARGS = 0;
 ENGINEINDEX = 0;
output;
run;

/* Add function to existing in-memory function list */
libname mydblib oracle sql_functions="EXTERNAL_APPEND=work.newfunc"
   sql_functions_copy=saslog;

Example 4: Add and Run the Netezza Variant of the SOUNDEX Function

Netezza supports the NYSIIS function, which is a variant of the SOUNDEX function and produces similar results. This example shows how to specify that NYSIIS should be called when the SOUNDEX function is passed to a Netezza DBMS. 

data work.newfunc;
SASFUNCNAME = "SOUNDEX";
SASFUNCNAMELEN = 7;
DBMSFUNCNAME = "NYSIIS";
DBMSFUNCNAMELEN = 6;
 FUNCTION_CATEGORY = "SCALAR";
 FUNC_USAGE_CONTEXT = "SELECT_LIST";
 FUNCTION_RETURNTYP = "CHAR";
 FUNCTION_NUM_ARGS = 1;
 CONVERT_ARGS = 0;
 ENGINEINDEX = 0;
output;
run;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

/* Add function to existing in-memory function list */
libname x netezza server=&server. database=&database. uid=&user. pwd=&password. 
   sql_functions="EXTERNAL_APPEND=work.newfunc" sql_functions_copy=saslog;

proc sql;
select distinct soundex('oliver') from x._v_dual;
quit;

/* query returns */
/*
NYSIIS
------
OLAVAR
*/
Last updated: February 3, 2026