What is the difference between NULL and MISSING in PROC SQL?

[Glen Chen]

Hello, folks:

Could anybody kindly explain to me the difference b/w "NULL" and
"MISSING" when used in PROC SQL?

I didn't find any difference so far. Below is an example:
The only difference between the two PROC SQL blocks is NULL/MISSING. I
was given the same results from the two SQLs.

Any help is appreciated.
Glen

*================;

data test;
do value = "a","b","" ,"d"; output; end;
run;

proc sql;
create table aaa as
select *
from test
where value is not NULL;
quit;

proc sql;
create table bbb as
select *
from test
where value is not MISSING;
quit;

[data _null_;]

Add FEEBACK to the PROC SQL statement and you will see that both where
statements are viewed by SQL as....

NOTE: Statement transforms to:

select TEST.value
from WORK.TEST
where TEST.value is not null;

[Michael S. Zdeb]

Hi...this has been the subject of a bunch of notes on SAS-L in the past.
Check the archives at...

http://listserv.uga.edu/cgi-bin/wa?S1=sas-l

Also, there's a paper at SAS tech support...

http://support.sas.com/rnd/warehousing/papers/resultsets.pdf

that discusses NULL versus MISSING in relational databases and SAS, and a
NESUG paper...

http://www.nesug.org/html/Proceedings/nesug98/dbas/p004.pdf

that discusses missing values in PROC SQL.


Mike Zdeb
***@Albany School of Public Health
1 University Drive
Rensselaer, NY 12144-3456
(P)518-402-6479
(F)630-604-1475




Glen Chen
<***@HOTMAIL
.COM> To
Sent by: "SAS(r) SAS-***@LISTSERV.UGA.EDU
Discussion" cc
<SAS-***@LISTSERV.U
GA.EDU> Subject
What is the difference between NULL
and MISSING in PROC SQL?
11/02/2005 11:08
AM


Please respond to
Glen Chen
<***@HOTMAIL
.COM>






Hello, folks:

Could anybody kindly explain to me the difference b/w "NULL" and
"MISSING" when used in PROC SQL?

I didn't find any difference so far. Below is an example:
The only difference between the two PROC SQL blocks is NULL/MISSING. I
was given the same results from the two SQLs.

Any help is appreciated.
Glen

*================;

data test;
do value = "a","b","" ,"d"; output; end;
run;

proc sql;
create table aaa as
select *
from test
where value is not NULL;
quit;

proc sql;
create table bbb as
select *
from test
where value is not MISSING;
quit;

[Glen Chen]

Thank you all for the helpful replies.

The NESUG paper is very helpful. It says:
" IS [NOT] NULL and IS [NOT] MISSING
predicates are two predicates especially
designed to deal with missing values. IS
[NOT] NULL is a SQL standard predicate
and IS [NOT] MISSING is SAS SQL
predicate. They are generic because they
can handle both numeric and character
variables. "

Glen