Macro Variables and System Options for Relational Databases

SQLGENERATION= System Option

Specifies whether and when SAS procedures generate SQL for in-database processing of source data.

Default value:	NONE DBMS='Teradata'
Valid in:	configuration file, SAS invocation, OPTIONS statement, SAS System Options window
DBMS Support:	DB2 under UNIX and PC Hosts, Oracle, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

SQLGENERATION=<(>NONE | DBMS <DBMS='engine1 engine2 ... enginen'>

<<EXCLUDEDB=engine | 'engine1 engine2 ... enginen'>>

<<EXCLUDEPROC="engine='proc1 proc2 ... procn' engine2='proc1 proc2 ... procn' enginen='proc1 proc2 ... procn' "> <)>>

SQLGENERATION=" "

Syntax Description

NONE

prevents those SAS procedures that are enabled for in-database processing from generating SQL for in-database processing. This is a primary state.

DBMS

allows SAS procedures that are enabled for in-database processing to generate SQL for in-database processing of DBMS tables through supported SAS/ACCESS engines. This is a primary state.

DBMS='engine1 engine2 ... enginen'

specifies one or more SAS/ACCESS engines. It modifies the primary state.

Restriction:

The maximum length of an engine name is 8 characters.

EXCLUDEDB=engine | 'engine1 engine2 ... enginen'

prevents SAS procedures from generating SQL for in-database processing for one or more specified SAS/ACCESS engines.

Restriction:

The maximum length of an engine name is 8 characters.

EXCLUDEPROC="engine='proc1 proc2 ... procn' enginen='proc1 proc2 ... procn' "

identifies engine-specific SAS procedures that do not support in-database processing.

Restrictions:

The maximum length of a procedure name is 16 characters.

An engine can appear only once, and a procedure can appear only once for a given engine.

" "

resets the value to the default that was shipped.

Details

Use this option with such procedures as PROC FREQ to indicate what SQL is generated for in-database processing based on the type of subsetting that you need and the SAS/ACCESS engines that you want to access the source table.

You must specify NONE and DBMS, which indicate the primary state.

The maximum length of the option value is 4096. Also, parentheses are required when this option value contains multiple keywords.

Not all procedures support SQL generation for in-database processing for every engine type. If you specify a setting that is not supported, an error message indicates the level of SQL generation that is not supported, and the procedure can reset to the default so that source table records can be read and processed within SAS. If this is not possible, the procedure ends and sets SYSERR= as needed.

You can specify different SQLGENERATION= values for the DATA= and OUT= data sets by using different LIBNAME statements for each of these data sets.

Here is how SAS/ACCESS handles precedence.

Precedence of Values for SQLGENERATION= LIBNAME and System Options
LIBNAME Option	PROC EXCLUDE on System Option?	Engine Type	Engine Specified on System Option	Resulting Value	From (option)
not set NONE DBMS	yes	database interface	NONE DBMS	NONE EXCLUDEDB	system
NONE	no			NONE	LIBNAME
DBMS				DBMS	LIBNAME
not set			NONE	NONE	system
			DBMS	DBMS
		no SQL generated for this database host or database version	NONE DBMS	NONE
NONE DBMS		no SQL generated for this database host or database version			LIBNAME
not set		Base			system
NONE DBMS		Base			LIBNAME

Examples

Here is the default that is shipped with the product.

options sqlgeneration='' ; 
proc options option=sqlgeneration 
 run;

SAS procedures generate SQL for in-database processing for all databases except DB2 in this example.

options sqlgeneration='' ; 
options sqlgeneration=(DBMS EXCLUDEDB='DB2') ;
proc options option=sqlgeneration ;
run;

In this example, in-database processing occurs only for Teradata, but SAS procedures generate no SQL for in-database processing.

options sqlgeneration='' ; 
options SQLGENERATION = (NONE DBMS='Teradata') ; 
proc options option=sqlgeneration ;
run;

In this next example, SAS procedures do not generate SQL for in-database processing even though in-database processing occurs only for Teradata.

options sqlgeneration='' ; 
Options SQLGENERATION = (NONE DBMS='Teradata' EXCLUDEDB='DB2') ; 
proc options option=sqlgeneration ;
run;

For this example, PROC1 and PROC2 for Oracle do not support in-database processing, SAS procedures for Oracle that support in-database processing do not generate SQL for in-database processing, and in-database processing occurs only for Teradata.

options sqlgeneration='' ; 
Options SQLGENERATION = (NONE EXCLUDEPROC="oracle='proc1,proc2'" 
        DBMS='Teradata' EXCLUDEDB='ORACLE') ; 
proc options option=sqlgeneration ;
run;