- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So when null_var is missing, you want null_comp_result to equal zero, correct?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
yes, furthermore i want all comparisons with null result in False
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Oh, and to note you also have the missing() function which can compare either character or numeric variables as missing:
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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