Missing = Null?

[mnew]

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

[art297]

According to this article they are: http://www.sascommunity.org/wiki/Tips:Use_IS_MISSING_and_IS_NULL_with_Numeric_or_Character_Variables

[mnew]

Thank you for the help.

[scmebu]

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;