28 Potential Result Set Differences When Processing Null Data Chapter 3
For example, create a simple data set that consists of one observation and one
variable:
libname myoralib oracle user=testuser password=testpass;
data myoralib.table;
x=.; /*create a missing value */
run;
Then, print the data set using a WHERE clause, which SAS/ACCESS passes to the
DBMS for processing:
proc print data=myoralib.table;
where x<0;
run;
The log indicates that no observations were selected by the WHERE clause, because
Oracle interprets the missing value as the absence of data, and does not evaluate it
with the less-than (<) comparison operator.
When there is the potential for inconsistency, consider using one of the following
strategies:
Use the LIBNAME option DIRECT_SQL= to control whether the processing is
done by SAS or by the DBMS.
Use the Pass-Through Facility to ensure that the processing is done by the DBMS.
Add the "is not null" expression to WHERE clauses and ON clauses to ensure that
you will get the same result regardless of whether SAS or the DBMS does the
processing.
Note: Use the data set option NULLCHAR= to specify how the DBMS interprets
missing SAS character values when updating DBMS data or inserting rows into a
DBMS table.
You can use the first of these strategies to force SAS to process the data in the
example below:
libname myoralib oracle user=testuser password=testpass
direct_sql=nowhere; /* forces SAS to process WHERE clauses */
data myoralib.table;
x=.; /*create a missing value */
run;
Then, print the data set using a WHERE clause:
proc print data=myoralib.table;
where x<0;
run;
This time, the log indicates that one observation was read from the data set, because
SAS evaluates the missing value as satisfying the less-than-zero condition in the
WHERE clause.
