The DIRECT_SQL= LIBNAME option enables you to restrict the SQL code that is passed to a database when you perform a join or when you use a WHERE clause. Use of this option is required only in special circumstances.
For example, if you have NULL values in a DBMS column that is used in a WHERE clause, your results might differ based on whether WHERE processing occurs in the DBMS or in SAS. Many DBMSs remove NULL values before processing a WHERE clause, but SAS does not remove NULL values. To prevent WHERE clauses from being passed to the DBMS, specify DIRECT_SQL=NOWHERE. For more information, see Potential Result Set Differences When Processing Null Data.
The default value for the DIRECT_SQL= LIBNAME option is YES. PROC SQL attempts to pass SQL joins directly to the DBMS for processing. Other values for DIRECT_SQL= influence the nature of the SQL statements that PROC SQL tries to pass down to the DBMS or if it tries to pass anything at all.
PROC SQL does not attempt to pass SQL join queries to the DBMS. However, other SQL statements can be passed. If the MULTI_DATASRC_OPT= is in effect, the generated SQL can also be passed.
PROC SQL does not attempt to pass any SQL directly to the DBMS for processing.
PROC SQL does not pass any statement to the DBMS if that statement contains a function other than a summary function. Normally, PROC SQL attempts to pass down any functions coded in the SQL to the DBMS, provided the DBMS supports the given function.
PROC SQL does not attempt to pass SQL join queries to the DBMS. Other SQL statements can be passed down, however. If the MULTI_DATASRC_OPT= is in effect, the generated SQL can be passed.
PROC SQL does not attempt to pass any multiple outer joins to the DBMS for direct processing. Other SQL statements can be passed, however, including portions of a multiple outer join.
PROC SQL attempts to pass SQL to the DBMS, including SQL joins. However, it does not pass any WHERE clauses that are associated with the SQL statement. This causes any join that is attempted with direct processing to fail.
PROC SQL passes generated SQL to the DBMS for processing. However, it tries only once. It does not try again if the first attempt fails.
PROC SQL automatically attempts to pass the SQL join query to the DBMS. This is the default setting for this option. The join attempt could fail because of a DBMS return code. If this happens, PROC SQL attempts to open both tables individually and perform the join in SAS.