Specifies whether generated SQL is passed to the DBMS for processing.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | YES |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | SQL_FUNCTIONS= LIBNAME option |
Table of Contents
specifies that generated SQL from PROC SQL is not passed to the DBMS for processing. This is the same as specifying the value NOGENSQL.
prevents SQL statements from being passed to the DBMS for processing when they contain functions.
prevents PROC SQL from generating SQL to be passed to the DBMS for processing.
specifies that generated SQL is not passed to the DBMS for processing. This includes SQL that is generated from PROC SQL, SAS functions that can be converted into DBMS functions, joins, and WHERE clauses.
prevents WHERE clauses from being passed to the DBMS for processing. This includes SAS WHERE clauses and PROC SQL generated or PROC SQL specified WHERE clauses.
specifies that PROC SQL does not attempt to pass any multiple outer joins to the DBMS for processing. Other join statements might be passed down, however, including portions of a multiple outer join.
specifies that PROC SQL passes generated SQL to the DBMS for processing. PROC SQL tries only once, however. It does not try again if the first attempt fails.
specifies that generated SQL from PROC SQL is passed directly to the DBMS for processing.
By default, processing is passed to the DBMS whenever possible. The database is generally able to process the functionality more efficiently than SAS. In some instances, however, you might not want the DBMS to process the SQL. For example, the presence of null values in the DBMS data might cause different results depending on whether the processing takes place in SAS or in the DBMS. If you do not want the DBMS to handle the SQL, use DIRECT_SQL= to force SAS to handle some or all SQL processing.
If you specify DIRECT_SQL=NOGENSQL, PROC SQL does not generate DBMS SQL. This means that SAS functions, joins, and DISTINCT processing that occur within PROC SQL are not passed to the DBMS for processing. (SAS functions outside PROC SQL can still be passed to the DBMS.) However, if PROC SQL contains a WHERE clause, the WHERE clause is passed to the DBMS, if possible. Unless you specify DIRECT_SQL=NOWHERE, SAS attempts to pass all WHERE clauses to the DBMS.
If you specify more than one value for this option, separate the values with spaces and enclose the list of values in parentheses. For example, you could specify DIRECT_SQL=(NOFUNCTIONS NOWHERE).
DIRECT_SQL= overrides the SQL_FUNCTIONS= LIBNAME option. If you specify SQL_FUNCTIONS=ALL and DIRECT_SQL=NONE, no functions are passed.
This example prevents the DBMS from processing a join between two tables by setting DIRECT_SQL=NOGENSQL. SAS processes the join instead.
proc sql;
create view work.v as
select tab1.deptno, dname from
mydblib.table1 tab1,
mydblib.table2 tab2
where tab1.deptno=tab2.deptno
using libname mydblib oracle user=myusr1
password=mypwd1 path=mysrv1 direct_sql=nogensql; libname mydblib oracle user=myusr1 password=mypwd1 direct_sql=nofunctions;
proc print data=mydblib.tab1;
where lastname=soundex ('Paul');