BookmarkSubscribeRSS Feed
mnew
Calcite | Level 5

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

3 REPLIES 3
mnew
Calcite | Level 5

Thank you for the help.

scmebu
SAS Employee

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 10014 views
  • 3 likes
  • 3 in conversation