Data Set Options for Relational Databases DBKEY= Data Set Option 177
set x.dbtab(dbindex=yes) key=a;
set mydblib.dbtab(dbindex=yes) key=a;
run;
The key is validated against the list from the DBMS. If a
is an index, then a pass down
occurs. Otherwise the join takes place in SAS.
The following example demonstrates the use of DBINDEX in PROC SQL:
proc sql;
select * from s1 aa, x.dbtab(dbindex=yes) bb where aa.a=bb.a;
select * from s1 aa, mylib.dbtab(dbindex=yes) bb where aa.a=bb.a;
/*or*/
select * from s1 aa, x.dbtab(dbindex=a) bb where aa.a=bb.a;
select * from s1 aa, mylib.dbtab(dbindex=a) bb where aa.a=bb.a;
See Also
To assign this option to a group of relational DBMS tables or views, see the
LIBNAME option “DBINDEX= LIBNAME Option” on page 88.
DBKEY= Data Set Option
Can improve performance when processing a join that involves a large DBMS table and a small
SAS data set or DBMS table (by specifying a key column to optimize DBMS retrieval).
Valid in:
DATA and PROC steps (when accessing DBMS data using SAS/ACCESS
software)
DBMS support: DB2 OS/390, DB2 UNIX/PC, Informix, ODBC, OLE DB, Oracle, Microsoft
SQL Server, SYBASE, Teradata
Default value: none
Syntax
DBKEY=(<’>column-1<’> <... <’>column-n<’>>)
Syntax Description
column
used by SAS to build an internal WHERE clause to search for matches in the DBMS
table based on the key column. For example:
select * from sas.a, dbms.b(dbkey=x) where a.x=b.x;
In the example, DBKEY specifies column
x, which matches the key column
designated in the WHERE clause. However, if the DBKEY column does NOT match
the key column in the WHERE clause, then DBKEY is not used.
Example
The following is an example of using DBKEY= in a data step modify context:
