topic Re: Comparing with null (missing) values in proc sql in SAS Programming

Comparing with null (missing) values in proc sql

EtoUspeh — Thu, 09 Aug 2018 08:21:28 GMT

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?

Re: Comparing with null (missing) values in proc sql

PeterClemmensen — Thu, 09 Aug 2018 08:26:55 GMT

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

Re: Comparing with null (missing) values in proc sql

EtoUspeh — Thu, 09 Aug 2018 08:33:08 GMT

Draycut,

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

Re: Comparing with null (missing) values in proc sql

RW9 — Thu, 09 Aug 2018 08:42:29 GMT

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.

Re: Comparing with null (missing) values in proc sql

RW9 — Thu, 09 Aug 2018 08:46:58 GMT

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#p06ybg84o0asa4n17l9ieauk58hb.htm

Re: Comparing with null (missing) values in proc sql

Kurt_Bremser — Thu, 09 Aug 2018 08:49:45 GMT

@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