CODEWITHSUNDEEP
saswhere-clause
Mortz
Mortz

Reputation: 4879

Using where expression with not in operator in SAS

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.

Upvotes: 1

Views: 7814

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12691

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;

Upvotes: 1

Related Questions