Comparing with null (missing) values in proc sql

[EtoUspeh]

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?

[PeterClemmensen]

So when null_var is missing, you want null_comp_result to equal zero, correct?

[EtoUspeh]

Draycut,

yes, furthermore i want all comparisons with null result in False

[RW9 · Accepted solution]

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.

[RW9]

Oh, and to note you also have the missing() function which can compare either character or numeric variables as missing:

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p06ybg84o0a...

[Kurt_Bremser]

---

@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