Specifies whether and when SAS procedures generate SQL for in-database processing of source data.
Valid in: | configuration file, SAS invocation, OPTIONS statement, SAS System Options window |
Categories: | Data Access |
System Administration: Performance | |
Default: | (NONE DBMS='TERADATA DB2 ORACLE NETEZZA ASTER GREENPLM HADOOP SAPHANA') |
Restrictions: | Parentheses are required when this option value contains multiple keywords. |
The maximum length of the option value is 4096 characters. | |
For DBMS= and EXCLUDEDB= values, the maximum length of an engine name is eight characters. For the EXCLUDEPROC= value, 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. | |
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. | |
If you are using the Metadata LIBNAME Engine, the only valid SQLGENERATION= modifiers are NONE and DBMS. The engine ignores the DBMS=, EXCLUDEDB=, and EXCLUDEPROC= modifiers. | |
Requirement: | You must specify NONE or DBMS as the primary state. |
Interactions: | Use this option with such procedures as PROC FREQ to indicate that SQL is generated for in-database processing of DBMS tables through supported SAS/ACCESS engines. |
You can specify different SQLGENERATION= values for the DATA= and OUT= data sets by using different LIBNAME statements for each of these data sets. | |
Data source: | Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, Netezza, Oracle, SAP Hana, Teradata |
Tip: | After you set a required value (primary state), you can specify optional values (modifiers). |
See: | SQLGENERATION= LIBNAME option (includes examples), “Running In-Database Procedures” in SAS In-Database Products: User’s Guide |
prevents those SAS procedures that are enabled for in-database processing from generating SQL for in-database processing. This is a primary state.
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.
resets the value to the default that was shipped.
specifies one or more SAS/ACCESS engines. It modifies the primary state.
prevents SAS procedures from generating SQL for in-database processing for one or more specified SAS/ACCESS engines.
prevents one or more specified SAS procedures from generating SQL for in-database processing for one or more specified SAS/ACCESS engines.
LIBNAME Option
|
PROC EXCLUDE on System
Option?
|
Engine Specified on
System Option
|
Resulting Value
|
From (option)
|
---|---|---|---|---|
not set
|
yes
|
NONE
|
NONE
|
system
|
DBMS
|
EXCLUDEPROC
|
|||
NONE
|
NONE
|
NONE
|
LIBNAME
|
|
DBMS
|
||||
DBMS
|
NONE
|
EXCLUDEPROC
|
system
|
|
DBMS
|
||||
not set
|
no
|
NONE
|
NONE
|
|
DBMS
|
DBMS
|
|||
NONE
|
NONE
|
NONE
|
LIBNAME
|
|
DBMS
|
||||
DBMS
|
NONE
|
DBMS
|
||
DBMS
|
options sqlgeneration=''; proc options option=sqlgeneration run;
options sqlgeneration=''; options sqlgeneration=(DBMS EXCLUDEDB='DB2'); proc options option=sqlgeneration; run;
options sqlgeneration=''; options SQLGENERATION=(NONE DBMS='Teradata'); proc options option=sqlgeneration; run;
options sqlgeneration=''; options SQLGENERATION = (NONE DBMS='Teradata Oracle' EXCLUDEPROC="oracle='proc1 proc2'"); proc options option=sqlgeneration; run;