The LIBNAME Statement for Relational Databases

SQL_FUNCTIONS= LIBNAME Option

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

Default value:	none
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	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

Syntax
Syntax Description
Details
Limitations
Examples
See Also

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

Use of this option can cause unexpected results, especially if used for NULL processing and date, time, and timestamp handling. 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 documentation for your SAS/ACCESS interface for list of functions that it supports.

Limitations

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.

This table provides additional details to keep in mind when you add to or modify the SAS data set.

Variable	Required*	Optional**	Read-Only**	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.
* An error results when a value is missing. ** For new and existing functions.

Examples

You can use EXTERNAL_APPEND= to include one or more existing functions to the in-memory function list and EXTERNAL_REPLACE= to exclude 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;

In this next 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;

This example shows how to 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;