Data Set Options for Relational Databases |
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
|
DBNULL=<_ALL_=YES | NO >
| ( <column-name-1=YES | NO> <...<column-name-n=YES |
NO>>)
|
-
_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.
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.
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;
NULLCHAR= Data Set Option
NULLCHARVAL= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.