Data Set Options for Relational Databases

BL_SUPPRESS_NULLIF= Data Set Option

Indicates whether to suppress the NULLIF clause for the specified columns to increase performance when a table is created.

Default value:	NO
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle

Syntax
Syntax Description
Details
Example
See Also

Syntax

BL_SUPPRESS_NULLIF=<_ALL_=YES | NO> | ( <column-name-1=YES | NO> <column-name-n=YES | NO>...)

Syntax Description

YES: column-name-1=YES indicates that the NULLIF clause should be suppressed for the specified column in the table.
NO: column-name-1=NO indicates that the NULLIF clause should not be suppressed for the specified column in the table.
_ALL_: specifies that the YES or NO applies to all columns in the table.

Details

To specify this option, you must first set BULKLOAD=YES.

If you specify more than one column name, the names must be separated with spaces.

The BL_SUPPRESS_NULLIF= 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 that you specified for the column.

Example

This example uses the BL_SUPPRESS_NULLIF= option in the DATA step to suppress the NULLIF clause for columns C1 and C5 in the table.

data x.suppressnullif2_yes (bulkload=yes BL_SUPPRESS_NULLIF=(c1=yes c5=yes));
run;

The next example uses the BL_SUPPRESS_NULLIF= option in the DATA step to suppress the NULLIF clause for all columns in the table.

libname x oracle user=dbitest pw=tiger path=lupin_o9010;

%let num=1000000;   /* 1 million rows */

data x.testlmn ( bulkload=yes
                  BL_SUPPRESS_NULLIF=( _all_ =yes )
                  rename=(year=yearx) );
  set x.big1mil (obs= &num ) ;
run;