DBNULL= Data Set Option

When a table is created, this option indicates whether a null is a valid value for the specified columns.

Valid in:	DATA and PROC steps
Default:	data source-specific
Supports:	DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Syntax Description

Details

Examples

Example 1: Preventing Specific Columns from Accepting Null Values

Example 2: Preventing All Columns from Accepting Null Values

Syntax

DBNULL=<_ALL_=YES | NO>
| ( <column-name-1=YES | NO > <<column-name-2=YES | NO> ...> )

Syntax Description

_ALL_

specifies that the YES or NO applies to all columns in the table. (This is valid for Oracle and Teradata only.)

column-name

specifies that the YES or NO applies to the name a column or columns only.

YES

specifies that a null is not valid for the specified columns in the table.

NO

specifies that a null is not valid for the specified columns in the table.

Details

This option is valid only when you are creating 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. Therefore, 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

Example 1: Preventing Specific Columns from Accepting Null Values

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;

Example 2: Preventing All Columns from Accepting Null Values

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.

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