SQLGENERATION= SAS System Option

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

Valid in: SAS 9.4: configuration file, SAS invocation, OPTIONS statement, SAS System Options window
SAS Viya 3.5: configuration file, SAS invocation, OPTIONS statement, SASV9_OPTIONS environment variable
Categories: Data Access
System Administration: Performance
Default: SAS 9.4: (NONE DBMS='TERADATA DB2 ORACLE NETEZZA ASTER GREENPLM HADOOP SAPHANA IMPALA HAWQ POSTGRES REDSHIFT SQLSVR VERTICA BIGQUERY SNOW YBRICK SPARK MYSQL')
SAS Viya 3.5: (NONE DBMS='TERADATA DB2 ORACLE NETEZZA ASTER GREENPLM HADOOP SAPHANA IMPALA HAWQ POSTGRES REDSHIFT SQLSVR VERTICA SNOW')
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 value 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.
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, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Microsoft SQL Server, MySQL, Netezza, Oracle, PostgreSQL, SAP HANA, Snowflake, Spark, Teradata, Vertica, Yellowbrick
Notes: Support for Google BigQuery and Yellowbrick was added in SAS 9.4M7.
Support for MySQL was added in SAS 9.4M8.
Tip: After you specify 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

Table of Contents

Syntax

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 are the values that you specify for each engine. The values are not case-specific.

SQLGENERATION Values to Specify for Each Engine

Engine

SQLGENERATION Value

Amazon Redshift

REDSHIFT

Aster

ASTER

DB2

DB2

Google BigQuery

BIGQUERY

Greenplum

GREENPLUM

Hadoop

HADOOP

HAWQ

HAWQ

Impala

IMPALA

Microsoft SQL Server

SQLSVR

MySQL

MYSQL

Netezza

NETEZZA

Oracle

ORACLE

PostgreSQL

POSTGRES

SAP HANA

SAPHANA

Snowflake

SNOW

Teradata

TERADATA

Vertica

VERTICA

Yellowbrick

YBRICK

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 specified

yes

NONE

NONE

system

DBMS

EXCLUDEPROC

NONE

NONE

NONE

LIBNAME

DBMS

DBMS

NONE

EXCLUDEPROC

system

DBMS

not specified

no

NONE

NONE

DBMS

DBMS

NONE

NONE

NONE

LIBNAME

DBMS

DBMS

NONE

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;
Last updated: February 3, 2026