LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2002, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page