DBNULL= Data Set Option

« Back to Glossary Index

DBNULL= option indicates whether NULL is a valid value for the specified columns when a table is created.

DBNULL=<em><a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n02dxkald2zj41n1egrcaaeskdp1.htm#n1mnrhmcf3nsurn1gqn5fw4hvyq8">_ALL_=</a></em><a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n02dxkald2zj41n1egrcaaeskdp1.htm#p1t19em2fjfm1jn1190tya9pqg5h">YES</a> | <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n02dxkald2zj41n1egrcaaeskdp1.htm#n0978mvz253cjun10a6pb1qmc743">NO</a>

Syntax Description

_ALL_ – specifies that the YES or NO applies to all columns in the table. You can specify _ALL_=YES or _ALL_=NO in combination with one or more columns.

YES – specifies that the NULL value is valid for the specified column in the DBMS table.

NO – specifies that the NULL value is not valid for the specified column in the DBMS table.

This option is valid only for creating DBMS tables. If you specify more than one column name, you must separate them with spaces and include the list in parentheses.

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


data mydblib.mydept2(dbnull=(empid=no jobcode=no));
   set mydblib.employees;

In this example, the DBNULL= option is used 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.

« Back to Glossary Index