When using a WHERE statement or an SQL WHERE clause, you might encounter an issue in which zero rows are returned.

This issue occurs under the following conditions:

• The WHERE statement uses NOT IN (list-of-values), and one of the values listed is a missing value.
• The WHERE statement uses BETWEEN minimum-value AND maximum-value and neither value is a missing value.

Here is an example:

data work.sample ;
   do x = . , 0 , 1 , 2 , 3 ;
      output ;
   end ;
run ;
data work.results ; 
   set sample ;
   where x not in ( . , 1 ) and x between 0 and 2 ;
run ;

When you run this code, you expect to see two rows returned (x=0 and x=2) in the work.results table, but zero rows are returned:

6    data results ;
7       set sample ;
8       where x not in ( . , 1 ) and x between 0 and 2 ;
9    run ;

NOTE: There were 0 observations read from the data set WORK.SAMPLE.
      WHERE (x>=0 and x<.);
NOTE: The data set WORK.RESULTS has 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

Notice that the message about the WHERE statement shows WHERE (x>=0 and x<.).  Because it is not possible that X can be less than missing, this statement can never be true.

In this example, there is no reason to use the missing value in the NOT IN expression. So, you can revise the WHERE statement to the following:

where x not in ( 1 ) and x between 0 and 2 ;

When you run this statement, the SAS^® log shows that two rows are returned. The message about the WHERE statement shows the following:

WHERE ((x>=0 and x<1) or (x>1 and x<=2));

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.4_M4 is available at: