It appears that SAS evaluates comparison with null values as True
proc sql;
select
case when null_var < 123 then 1 else 0 end as null_comp_result
from my_table;
quit;
null_var contains missing values, the query returns column of 1's.
Is it possible to somehow change SAS setting so comparing to null will give False?
Remember you are talking about two separate things here.
Null is an SQL/Database thing. SAS treats missing numeric cells as ., and missing character strings as "". The reason your logic equates to true is:
null_var=.
. < 123 = true.
The answer to this is to use SAS thinking:
case when . < null_var < 123 then 1 else 0 end as null_comp_result.
So when null_var is missing, you want null_comp_result to equal zero, correct?
Remember you are talking about two separate things here.
Null is an SQL/Database thing. SAS treats missing numeric cells as ., and missing character strings as "". The reason your logic equates to true is:
null_var=.
. < 123 = true.
The answer to this is to use SAS thinking:
case when . < null_var < 123 then 1 else 0 end as null_comp_result.
Oh, and to note you also have the missing() function which can compare either character or numeric variables as missing:
@EtoUspeh wrote:
It appears that SAS evaluates comparison with null values as True
proc sql;
select
case when null_var < 123 then 1 else 0 end as null_comp_result
from my_table;
quit;null_var contains missing values, the query returns column of 1's.
Is it possible to somehow change SAS setting so comparing to null will give False?
Per definition, a missing numerical value in SAS is smaller than any non-missing value.
See (eg) http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000989180.htm
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: