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)
Default:	DBMS-specific
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	NULLCHAR= data set option, NULLCHARVAL= data set option

Syntax

Details

Examples

Example 1: Prevent Specific Columns from Accepting Null Values

Example 2: Prevent All Columns from Accepting Null Values

Syntax

DBNULL=<_ALL_=YES | NO >
| ( <column-name-1=YES | NO> <…<column-name-n=YES | NO> > )

Syntax Description

_ALL_ [valid only for Informix, Oracle, Sybase, Teradata]: specifies that the YES or NO applies to all columns in the table.

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, you must separate them with spaces.

The DBNULL= option processes values from left to right. If you specify a column name twice or if you use the _ALL_ value, the last value overrides the first value that you specified for the column.

Examples

Example 1: Prevent Specific Columns from Accepting Null Values

In this example, you can use the DBNULL= option to prevent the EMPID and JOBCODE columns in the new MYDBLIB.MYDEPT2 table 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;

Example 2: Prevent All Columns from Accepting Null Values

In this 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.

data mydblib.mydept3(dbnull=(_ALL_=no jobcode=YES));
   set mydblib.employees;
run;