SQLGENERATION= System Option

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
Categories:	System Administration: Performance
Default:	(NONE DBMS=''TERADATA DB2 ORACLE NETEZZA ASTER GREENPLM HADOOP SAPHANA IMPALA HAWQ POSTGRES REDSHIFT SQLSVR VERTICA'')
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. The procedure can then 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.
Interactions:	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:	Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Microsoft SQL Server, Netezza, Oracle, PostgreSQL, SAP HANA, Teradata, Vertica
Note:	Support for Impala and HAWQ was added in the third maintenance release for SAS 9.4.
Tip:	After you set a required value (primary state), you can specify optional values (modifiers).
See:	SQLGENERATION= LIBNAME option (includes examples)
See:	“Running In-Database Procedures” in SAS In-Database Products: User’s Guide

Syntax

Syntax

SQLGENERATION=<(>NONE | DBMS

<DBMS='engine1 engine2…enginen'>

<EXCLUDEDB=engine | 'engine1…enginen'>

<EXCLUDEPROC="engine='proc1…procn' enginen='proc1…procn' "><)>

SQLGENERATION=" "

Required Arguments

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.

Note: As a best practice, run as many calculations in-database as possible. Processing that is run in-database generally results in better performance.

" "

resets the value to the default that was shipped.

Optional Arguments

DBMS='engine1…engineN'

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

EXCLUDEDB=engine | 'engine1…engineN'

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

EXCLUDEPROC="engine='proc1…procN' engineN='proc1…procN' "

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

Details

Here is how SAS/ACCESS handles precedence between the LIBNAME and system option.

Precedence of Values for SQLGENERATION= LIBNAME and System Options
LIBNAME Option	PROC EXCLUDE on System Option?	Engine Specified on System Option	Resulting Value	From (option)
not set	yes	NONE	NONE	system
not set		DBMS	EXCLUDEPROC	system
NONE		NONE	NONE	LIBNAME
NONE		DBMS	NONE	LIBNAME
DBMS		NONE	EXCLUDEPROC	system
DBMS		DBMS	EXCLUDEPROC
not set	no	NONE	NONE
not set		DBMS	DBMS
NONE		NONE	NONE	LIBNAME
NONE		DBMS	NONE
DBMS		NONE	DBMS
DBMS		DBMS	DBMS

Example

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. SAS procedures that are run on other databases do not generate SQL for in-database processing.

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

For this example, SAS procedures generate SQL for Teradata and Oracle in-database processing. However, no SQL is generated for PROC1 and PROC2 in Oracle.

options sqlgeneration='';
options SQLGENERATION = (NONE DBMS='Teradata Oracle'
   EXCLUDEPROC="oracle='proc1 proc2'");
proc options option=sqlgeneration;
run;