Using where expression with not in operator in SAS

Question

I have a database table, called CityData, which is queried using SAS through an ODBC. The table has a column, City, which has Missing/Null values. The following data step in SAS does not give the expected results -

Data New;
set CityData;
where pop> 10000 and City not in ('Mumbai')
run;

The above code excludes Null values from the output dataset. The following code, however, works as expected

Data New;
set CityData;
where pop > 10000 and (City not in ('Mumbai') or City is Null);
run;

Why? I am using a Windows SAS version 9.4.

Answer 1

It's due to the way the DBMS evaluates nulls. The libname engine sends your where statement back via implicit pass-through, and this evaluates to:

where pop> 10000 and City ne 'Mumbai'

null <> 'Mumbai' = null, hence nothing returned.

You are also missing a semicolon from your where statement.

Data New;
set CityData;
where pop> 10000 and City not in ('Mumbai'); 
run;