SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EtoUspeh
Fluorite | Level 6

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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

EtoUspeh
Fluorite | Level 6
Draycut,

yes, furthermore i want all comparisons with null result in False
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

@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

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 15653 views
  • 0 likes
  • 4 in conversation