SQLGENERATION= System Option

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

Valid in:	OPTIONS statement, SASV9_OPTIONS environment variable
Categories:	Data Access
Categories:	System Administration: Performance
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.
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, DB2, Google BigQuery, Greenplum, Hadoop, Impala, Microsoft SQL Server, MySQL, Netezza, Oracle, PostgreSQL, SAP HANA, Snowflake, Spark, Teradata, Vertica, Yellowbrick
Tip:	After you specify a required value (primary state), you can specify optional values (modifiers).
See:	SQLGENERATION= LIBNAME option
See:	“Running In-Database Procedures” in SAS In-Database Products: User’s Guide

Table of Contents

Syntax

SQLGENERATION=<(>NONE | DBMS

<DBMS='engine1 <engine2 ...>'>

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

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

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 <engine2 ...>'

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

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

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

EXCLUDEPROC="engine='proc1 <proc2 ...>' <engine2='proc1 <proc2 ...>'> "

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
DB2	DB2
Google BigQuery	BIGQUERY
Greenplum	GREENPLM
Hadoop	HADOOP
Impala	IMPALA
Microsoft SQL Server	SQLSVR
MySQL	MYSQL
Netezza	NETEZZA
Oracle	ORACLE
PostgreSQL	POSTGRES
SAP HANA	SAPHANA
Snowflake	SNOW
Spark	SPARK
Teradata	TERADATA
Vertica	VERTICA

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
not specified		DBMS	EXCLUDEPROC	system
NONE		NONE	NONE	LIBNAME
NONE		DBMS	NONE	LIBNAME
DBMS		NONE	EXCLUDEPROC	system
DBMS		DBMS	EXCLUDEPROC
not specified	no	NONE	NONE
not specified		DBMS	DBMS
NONE		NONE	NONE	LIBNAME
NONE		DBMS	NONE
DBMS		NONE	DBMS
DBMS		DBMS	DBMS

Examples

Example 1: View the Default Value

Here is code to see the default value that is shipped with the product.

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

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

Example 2: Restrict In-Database Processing for an Engine

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;

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

Example 3: Restrict In-Database Processing for All Engines but One

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;

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

Example 4: Enable In-Database Processing for Multiple Engines but Restrict Processing for Some Procedures

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

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

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

Last updated: September 16, 2026