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.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	NO
Requirement:	To specify this option, you must first set BULKLOAD=YES. If you specify more than one column name, you must separate the names with spaces.
Data source:	Oracle
See:	BULKLOAD= data set option

Syntax

Details

Examples

Example 1: Suppress NULLIF for Specific Table Columns

Example 2: Suppress NULLIF for All Table Columns

Syntax

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

Syntax Description

column-name-1=YES: specifies that the NULLIF clause should be suppressed for the specified column in the table.

column-name-1=NO: specifies that the NULLIF clause should not be suppressed for the specified column in the table.

_ALL_: specifies that the YES or NO value applies to all columns in the table.

Details

This 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.

CAUTION:

If you set this option to YES and try to insert null values, unpredictable values are inserted into the column.

Examples

Example 1: Suppress NULLIF for Specific Table Columns

In this example, BL_SUPPRESS_NULLIF= in the DATA step suppresses 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;

Example 2: Suppress NULLIF for All Table Columns

In this example, BL_SUPPRESS_NULLIF= in the DATA step suppresses 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;