SQL_FUNCTIONS= LIBNAME Statement Option

Specifies that the functions that match those supported by SAS should be passed to the data source.

Valid in:	LIBNAME statement
Default:	NONE
Supports:	DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Syntax Description

Details

Syntax

SQL_FUNCTIONS= ALL

Syntax Description

ALL

specifies that functions that match those that are supported by SAS should be passed to the data source.

Details

DB2 under UNIX and PC, ODBC Details: When SQL_FUNCTIONS= is set to ALL, only the functions that are supported by the database table drivers are passed. Only a fraction of the functions might be available.

DATE	TODAY	QTR	COMPRESS	SUBSTR
DATEPART	DAY	SECOND	INDEX	TRANWRD
DATETIME	HOUR	WEEKDAY	LENGTH	TRIMN
TIME	MINUTE	YEAR	REPEAT	MOD
TIMEPART	MONTH	BYTE	SOUNDEX

Use of this option can cause unexpected results, especially if it is used for NULL processing and date/time/timestamp handling. For example, the following SAS code that was executed without SQL_FUNCTIONS= enabled returns the SAS date 15308:

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

However, the same code that is executed with SQL_FUNCTIONS=ALL returns 2001-1-29, which is an ODBC date format. Care should be exercised when using this option.