Data Set Options for Relational Databases

DBNULL= Data Set Option

Indicates whether NULL is a valid value for the specified columns when a table is created.

Default value:	DBMS-specific
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	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

Syntax
Syntax Description
Details
Examples
See Also

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

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;

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;