182 DBNULL= Data Set Option Chapter 10
DBNULL= Data Set Option
Indicates whether NULL is a valid value for the specified columns when a table is created
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:
DBMS-specific
Syntax
DBNULL=<_ALL_ =YES | NO > | ( <column-name-1=YES | NO >
<...<column-name-n=YES | NO >>)
Syntax Description
_ALL_
specifies that the YES or NO applies to all columns in the table. (This is valid in the
interfaces to Informix, Oracle, SYBASE, and Teradata only.)
YES
specifies that the NULL value is valid for the specified columns in the DBMS table.
NO
specifies that the NULL value is not valid for the specified columns in the DBMS
table.
Details
This option is valid only for creating DBMS tables. If you specify more than one column
name, the names must be separated with spaces.
The DBNULL= option processes values from left to right, so if you specify a column
name twice, or if you use the _ALL_ value, the last value overrides the first value that
is specified for the column.
Examples
In the following example, by using the DBNULL= option, the EMPID and JOBCODE
columns in the new MYDBLIB.MYDEPT2 table are prevented from accepting null
values . If the EMPLOYEES table contains null values in the EMPID or JOBCODE
columns, the DATA step fails.
data mydblib.mydept2(dbnull=(empid=no jobcode=no));
set mydblib.employees;
run;
In the following example, all columns in the new MYDBLIB.MYDEPT3 table except
for the JOBCODE column are prevented from accepting null values. If the
EMPLOYEES table contains null values in any column other than the JOBCODE
column, the DATA step fails.
