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
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
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
proc sql noprint;
select 'case when nmiss('||compress(name)||')=count(*) then "'
||compress(name)||'" else " " end' into :nlst separated by "||' '||"
where libname='WORK' and memname='CLASS'
select distinct &nlst
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
20 data class;
21 set class;
SYMBOLGEN: Macro variable DROPLST resolves to Name ndummy cdummy
22 drop &droplst;
The above snip of log file shows how the var list was generated.
From: Electric Druids [mailto:electricdruids@HOTMAIL.COM]
Sent: Monday, June 17, 2002 9:15 AM
Subject: Unpopulated Variables
Just wondered if anyone knew of a macro which could check through a database
to flag up unpopulated varaibles?
Chat with friends online, try MSN Messenger: http://messenger.msn.com