Date:         Tue, 18 Jun 2002 12:58:31 -0700
Reply-To:     "Huang, Ya" <ya.huang@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <ya.huang@PFIZER.COM>
Subject:      Re: Unpopulated Variables (nmiss is different in sql and other pr
              oc)
Comments: To: Electric Druids <electricdruids@HOTMAIL.COM>
Content-Type: text/plain

Steve in a private mail confirmed that 'unpopulated' means
missing. So now this is a question of how to find the list
of variables with all missing values. Whenever there is a need
of var name list, I always think about SAS meta data first, meaning
sql dictionary tables. But how can we detect if a var's values
are all missing? Intuitively, I would try nmiss function. If nmiss=n,
then the var is all missing. nmiss is also an statistics for
proc means,summary etc., unfortunately,those proc cannot handle
character variables.

SQL, on the contrary, treat nmiss differently with proc means,
i.e. it treats character vars the same way as numeric var.
This is a great feature and could be used to find out which variable
has all missing value, regardless it's numeric or character.

Here is a piece of code that take advatage of meta data and nmiss
summary function.

options symbolgen;

data class;
 set sashelp.class;
name=' ';
ndummy=.;
cdummy=' ';

proc sql noprint;
select 'case when nmiss('||compress(name)||')=count(*) then "'
||compress(name)||'" else " " end' into :nlst separated by "||' '||"
from dictionary.columns
where libname='WORK' and memname='CLASS'
;
select distinct &nlst
into :droplst
from class
;

data class;
 set class;
 drop &droplst;
run;

proc print;
run;

-----------------

class data set has three 'unpopulated' vars: name, ndummy, cdummy,
but they are all gone after dropping the var list &droplst.

SYMBOLGEN:  Macro variable NLST resolves to case when nmiss(Name)=count(*)
then "Name" else " " end||' '||case when nmiss(Sex)=count(*) then "Sex" else
" " end||' '||case when nmiss(Age)=count(*) then "Age" else " " end||'
'||case when nmiss(Height)=count(*) then "Height" else " " end||' '||case
when nmiss(Weight)=count(*) then "Weight" else " " end||' '||case when
nmiss(ndummy)=count(*) then "ndummy" else " " end||' '||case when
nmiss(cdummy)=count(*) then "cdummy" else " " end
16   into :droplst
17   from class
18   ;
19
20   data class;
21    set class;
SYMBOLGEN:  Macro variable DROPLST resolves to Name         ndummy cdummy
22    drop &droplst;
23   run;

The above snip of log file shows how the var list was generated.

Kind regards

Ya Huang

-----Original Message-----
From: Electric Druids [mailto:electricdruids@HOTMAIL.COM]
Sent: Monday, June 17, 2002 9:15 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Unpopulated Variables


Hi,

Just wondered if anyone knew of a macro which could check through a database
to flag up unpopulated varaibles?

Regards,

Steve Gibbs

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com