SQL_FUNCTIONS= LIBNAME Option

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

Valid in:	SAS/ACCESS LIBNAME statement
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> or 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:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	SQL_FUNCTIONS_COPY= LIBNAME option

Syntax

Details

Examples

Example 1: Include and Replace Existing Functions

Example 2: Replace All SAS Functions with the Oracle Equivalent

Example 3: Add a New Function

Syntax

SQL_FUNCTIONS=ALL | "<libref.member>" | "EXTERNAL_APPEND=<libref.member>"

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> [not valid for Informix, OLE DB]: 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.

EXTERNAL_APPEND=<libref.member> [not valid for Informix, OLE DB]: 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.

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 date format. So you should exercise care 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.

Variable	Required1	Optional2	Read-Only2	Valid Values
SASFUNCNAME	X			Truncated to 32 characters if length is greater than 32
SASFUNCNAMELEN	X			Must correctly reflect the length of SASFUNCNAME
DBMSFUNCNAME	X			Truncated to 50 characters if length is greater than 50
DBMSFUNCNAMELEN	X			Must correctly reflect the length of DBMSFUNCNAME
FUNCTION_CATEGORY		X		AGGREGATE , CONSTANT, SCALAR
FUNC_USAGE_CONTEXT		X		SELECT_LIST, WHERE_ORDERBY
FUNCTION_RETURNTYP		X		BINARY, CHAR, DATE, DATETIME, DECIMAL, GRAPHIC, INTEGER, INTERVAL, NUMERIC, TIME, VARCHAR
FUNCTION_NUM_ARGS		X		0
CONVERT_ARGSS			X	Must be set to 0 for a newly added function.
ENGINEINDEX			X	Must remain unchanged for existing functions. Set to 0 for a newly added function.
1An error results when a value is missing.
2For 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;

SQL_FUNCTIONS= LIBNAME Option

Syntax

Syntax Description

Details

Examples

Example 1: Include and Replace Existing Functions

Example 2: Replace All SAS Functions with the Oracle Equivalent

Example 3: Add a New Function

See Also