Used in place of DBKEY to improve performance when processing a join between two data sources.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | NONE |
| Restriction: | This option is used only for PROC SQL processing. It is not used in DATA step processing. |
| Interaction: | This option is not used when a value is specified for the DBKEY= data set option. |
| 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, Spark, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | DBKEY= data set option, DBLARGETABLE= data set option |
Table of Contents
specifies use of an IN clause that contains values that were read from a smaller table. The clause is used to retrieve matching values in a larger table based on a key column that was designated in an equijoin.
turns off option functionality.
When you are processing a join between a SAS data set and a DBMS table, the SAS data set should be smaller than the DBMS table for optimal performance. However, if the SAS data set is larger than the DBMS table, the SAS data set is still used in the IN clause.
When SAS processes a join between two DBMS tables, SELECT COUNT (*) is issued to determine which table is smaller and whether it qualifies for an IN clause. You can use the DBLARGETABLE= data set option to prevent the SELECT COUNT (*) from being issued.
The IN clause currently has a limit of 4,500 unique values.
Specifying DBKEY= automatically overrides MULTI_DATASRC_OPT=.
DIRECT_SQL= can affect this option as well. If DIRECT_SQL=NONE or NOWHERE, the IN clause cannot be built and passed to the DBMS, regardless of the value of MULTI_DATASRC_OPT=. These values for DIRECT_SQL= prevent a WHERE clause from being passed.
Oracle: Oracle can handle an IN clause of only 1,000 values. It therefore divides larger IN clauses into multiple smaller IN clauses. The results are combined into a single result set. For example, if an IN clause contained 4,000 values, Oracle produces 4 IN clauses that contain 1,000 values each. A single result is produced, as if all 4,000 values were processed as a whole.
OLE DB: OLE DB restricts the number of values allowed in an IN clause to 255.
This example builds and passes an IN clause from the SAS table to the DBMS table, retrieving only the necessary data to process the join.
proc sql;
create view work.v as
select tab2.deptno, tab2.dname from
work.sastable tab1, dblib.table2 tab2
where tab12.deptno = tab2.deptno
using libname dblib oracle user=myusr1 password=mypwd1
multi_datasrc_opt=in_clause;
quit;This example prevents the building and passing of the IN clause to the DBMS. It requires all rows from the DBMS table to be brought into SAS to process the join.
libname dblib oracle user=myusr1 password=mypwd1 multi_datasrc_opt=none;
proc sql;
select tab2.deptno, tab2.dname from
work.table1 tab1,
dblib.table2 tab2
where tab1.deptno=tab2.deptno;
quit;