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) |
|---|---|
| Category: | Data Set Control |
| Default: | _ALL_=YES. (All columns are created without the NOT NULL constraint. That is, all columns allow NULL values.) |
| Restriction: | This option is valid for the Hadoop engine only when the HDMD_METADIR= connection option is not set. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | NULLCHAR= data set option, NULLCHARVAL= data set option |
Table of Contents
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.
specifies that the NULL value is valid for the specified column in the DBMS table.
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 if you use the _ALL_ value, the last value overrides the first value that you specified for the column.
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;