Date:         Mon, 16 Apr 2007 22:29:46 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Empty columns

On Mon, 16 Apr 2007 18:29:41 -0700, Frank DiIorio <frank_diiorio@YAHOO.COM>
wrote:

>This test program creates a test dataset (NUM2 and CHAR1 are all
>missing), then uses SQL first to generate some code snippets (macro
>var NAMES), then to run the snippets (CREATE TABLE).  Output dataset
>COUNTS has the same number of variables as input dataset TEST.  The
>values will be 1 if the sum of the number of missing values equals the
>number of observations in the dataset (i.e., all missing), 0
>otherwise.
>
>Regards,
>Frank DiIorio
>
>data test;
>input num1 num2 char1 $;
>datalines;
>1 . .
>. . .
>2 . .
>;
>
>proc sql noprint;
>     select catx(' ', '(sum(missing(', name, ")) = count(*)) as ",
>name)

Or, perhaps a tad more simply

      select catx(' ', 'count(', name, ") = 0 as ", name)

>     into :names separated by ', '
>     from dictionary.columns
>     where libname='WORK' & memname='TEST'
>     ;
>     create table counts as
>     select &names.
>     from test
>     ;
>quit;
>
>On Apr 16, 8:39 am, "finider...@gmail.com" <finider...@gmail.com>
>wrote:
>> My Goal :
>> I have a table with 400 columns and thousands lines.
>> I know some of the columns are empty.
>> I would like to write a SAS program which display anywhere the name of
>> the columns which are empty.
>>
>> I know there is a way of typing only one time the name of the columns,
>> using an array, like this :
>> [code]
>> array  cols{11} $200 STATE COUNTY PR_TYPE LAST_NM FIRST_NM
>>                         DEGREE TAX_ID ADDR CITY ZIP PHONE   ;
>> [/code]
>> But Is there any other way, smarter than typing 400 columns names ?
>>
>> PS : I am a french guy and I apologize for mistakes.