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
Category: System administration: Performance
Default: (NONE DBMS='ASTER DB2 GREENPLM NETEZZA ORACLE TERADATA')
Restriction: 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.
Data source: Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, Netezza, Oracle, Teradata
See: SQLGENERATION= LIBNAME option (includes examples), and also “Running In-Database Procedures” in SAS In-Database Products: User’s Guide

Syntax

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…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' "

identifies engine-specific SAS procedures that you do not want to run inside the database.

" "

resets the value to the default that was shipped.

Details

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 must specify NONE, DBMS, or both. One or both of these arguments indicates 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
not set
NONE
NONE
system
DBMS
DBMS
no SQL generated for this database host or database version
NONE
DBMS
NONE
NONE
DBMS
LIBNAME
not set
Base
system
NONE
DBMS
LIBNAME

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;