Help using Base SAS procedures

Missing = Null?

Reply
Frequent Contributor
Posts: 100

Missing = Null?

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

PROC Star
Posts: 7,356

Missing = Null?

Frequent Contributor
Posts: 100

Missing = Null?

Thank you for the help.

SAS Employee
Posts: 17

Missing = Null?

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;

Ask a Question
Discussion stats
  • 3 replies
  • 3433 views
  • 3 likes
  • 3 in conversation