Missing = Null?

mnew — Mon, 18 Jul 2011 22:14:32 GMT

Experts:

A beginner question. Is Null the same as Missing when we write Where clause in SAS? I tested a few. They seem to bring back the same results. Not sure if it is entirely true everywhere?

Thanks

Missing = Null?

mnew — Tue, 19 Jul 2011 22:52:47 GMT

Thank you for the help.

Missing = Null?

scmebu — Wed, 20 Jul 2011 14:32:11 GMT

NULL and MISSING, while very similar, have some semantic differences and use of WHERE statements/clauses can produce different results depending upon the engine (e.g., BASE, ACCESS). The following test case demonstrates this:

Test showing that a WHERE applied to a DBMS table via

an ACCESS engine will produce results different from those

produced when the WHERE is applied to a SAS data set.

The difference is caused by the treatment of NULL/missing

values. SAS considers missing values as less than all other

values while the DBMSs don't consider them at all.

data foo;

length i 8;

length x 8;

length y $ 1;

alpha="aAzZ09";

do i=1 to 30;

x=floor(ranuni(1)*3);

if ( ranuni(1) < 0.1 )

then x=.;

substr(y,1,1)=substr(alpha,floor(ranuni(1)*length(alpha))+1,1);

if ( ranuni(1) < 0.1 )

then y="";

output;

end;

drop alpha;

run;

proc print data=foo(where=(x < 2));

run;

Define library X, specifying an ACCESS engine...

data x.foo;

set foo;

run;

data bar;

set x.foo(where=(x < 2));

run;

proc sort data=bar;

by i;

run;

proc print data=bar;

run;

topic Missing = Null? in SAS Procedures

Missing = Null?

Missing = Null?

Missing = Null?

Missing = Null?