100 ENABLE_BULK= LIBNAME Option Chapter 9
prevents SQL statements from being passed to the DBMS for processing when they
contain functions.
NOMULTOUTJOINS
specifies that PROC SQL will not attempt to pass any multiple outer joins to the
DBMS for processing. Other join statements may be passed down however, including
portions of a multiple outer join.
Details
By default, processing is passed to the DBMS whenever possible, because the database
might be able to process the functionality more efficiently than SAS does. 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 of the
SQL processing.
If you specify DIRECT_SQL=NOGENSQL, then 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 libname option “SQL_FUNCTIONS= LIBNAME
Option” on page 126. If you specify SQL_FUNCTIONS=ALL and DIRECT_SQL=NONE,
no functions are passed.
Examples
The following example prevents a join between two tables from being processed by
the DBMS, by setting DIRECT_SQL=NOGENSQL. Instead, SAS processes the join.
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=testuser
password=testpass path=myserver direct_sql=nogensql;
The following example prevents a SAS function from being processed by the DBMS.
libname mydblib oracle user=testuser password=testpass direct_sql=nofunctions;
proc print data=mydblib.tab1;
where lastname=soundex (’Paul’);
ENABLE_BULK= LIBNAME Option
Enables the connection to process bulk copy when you load data into a SYBASE table
