Data Set Options for Relational Databases DBNULLKEYS= Data Set Option 183
data mydblib.mydept3(dbnull=(_ALL_=no jobcode=YES));
set mydblib.employees;
run;
See Also
“NULLCHAR= Data Set Option” on page 197
“NULLCHARVAL= Data Set Option” on page 198
DBNULLKEYS= Data Set Option
Controls the format of the WHERE clause when you use the DBKEY= data set option
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
Default value: LIBNAME setting
Syntax
DBNULLKEYS= YES | NO
Details
If there might be NULL values in the transaction table or the master table for the
columns that you specify in the DBKEY= option, then use DBNULLKEYS=YES. When
you specify DBNULLKEYS=YES and specify a column that is not defined as NOT
NULL in the DBKEY= data set option, SAS generates a WHERE clause that can find
NULL values. For example, if you specify DBKEY=COLUMN and COLUMN is not
defined as NOT NULL, SAS generates a WHERE clause with the following syntax:
WHERE ((COLUMN = ?) OR ((COLUMN IS NULL) AND (? IS NULL)))
This syntax enables SAS to prepare the statement once and use it for any value (NULL
or NOT NULL) in the column. Note that this syntax has the potential to be much less
efficient than the shorter form of the WHERE clause (presented below). When you
specify DBNULLKEYS=NO or specify a column that is defined as NOT NULL in the
DBKEY= option, SAS generates a simple WHERE clause.
If you know that there are no NULL values in the transaction table or the master
table for the columns you specify in the DBKEY= option, you can use
DBNULLKEYS=NO. If you specify DBNULLKEYS=NO and specify
DBKEY=COLUMN, SAS generates a shorter form of the WHERE clause (regardless of
whether or not the column specified in DBKEY= is defined as NOT NULL):
WHERE (COLUMN = ?)
See Also
To assign this option to a group of relational DBMS tables or views, see the
LIBNAME option “DBNULLKEYS= LIBNAME Option” on page 93.
