Date:         Mon, 23 Sep 1996 11:53:31 EDT
Reply-To:     whitloi1@WESTATPO.WESTAT.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Ian Whitlock <whitloi1@WESTATPO.WESTAT.COM>
Subject:      Re: Dropping Variables
Comments: To: Bill Shannon <shannon@OSLER.WUSTL.EDU>

    Subject: Dropping Variables
    Summary: Use macro with SQL
    Respondent: Ian Whitlock
 
    Bill Shannon <shannon@OSLER.WUSTL.EDU> asks
 
  >I have a data set with 50,000 rows and 1,100 variables.  The majority of
  >the variables have only 0 in each row, and I would like to have an
  >automatic way of identifying these columns and dropping them.  Any
  >suggestions.
 
    There is an article in Observations 1996 Q3 on dropping variables with
    missing values.  This is the same problem only the condition is "= 0".
    You could modify the macro presented there or use the one below.
 
    I wrote the macro DROPVAR in reaction to the older macro techniques
    demonstrated in the article.  To fit Bill's requirements I added the
    parameter COND and modified the line containing &COND.  Originally
    "&COND" was "IS NULL".  There is one disadvantage - DROPVAR requires
    version 6.11 or 6.09E.
 
    To test the macro I used.
 
        data w ;
           retain x y z 0 ;
           do i = 1 to 3 ;
              output ;
           end ;
        run ;
 
        %dropvar ( data = w , typ = n , cond = = 0 )
 
    The macro:
 
    %macro dropvar ( data = _last_ , out = , typ = B , cond = is null ) ;
 
        %local lib mem nobs vlist dlist i w ;
 
        %if %qupcase ( &data ) = _LAST_ %then %let data = &syslast ;
 
        %if %quote ( &out ) = %then %let out = &data ;
 
        %if %index ( &data , . ) %then
        %do ;
            %let lib = %upcase ( %scan ( &data , 1 , . ) ) ;
            %let mem = %upcase ( %scan ( &data , 2 , . ) ) ;
        %end ;
        %else
        %do ;
            %let lib = WORK ;
            %let mem = %upcase ( &data ) ;
        %end ;
 
        proc sql noprint ;
           select nobs into :nobs
              from dictionary.tables
              where libname = "&lib" and memname = "&mem"
           ;
 
           %if &nobs = %then
           %do ;
               %put ERROR: &lib..&mem does not exist - DROPVAR halting. ;
               %goto mexit ;
           %end ;
 
           select name into :vlist separated by ' '
              from dictionary.columns
              where libname = "&lib" and memname = "&mem"
                    %if %upcase(%substr(&typ,1,1)) = N %then
                       and type = 'num'
                    %else
                    %if %upcase(%substr(&typ,1,1)) = C %then
                       and type = 'char' ;
           ;
 
           create table __vars as
           select
               %let i = 1 ;
               %let w = %qscan ( &vlist , &i ) ;
               %do %while ( %quote (&w) ^= ) ;
                  %if &i ^= 1 %then , ;
                    sum ( &w &cond ) as &w
                  %let i = %eval ( &i + 1 ) ;
                  %let w = %qscan ( &vlist , &i ) ;
               %end ;
           from &mem ;
        quit ;
 
        proc transpose data = __vars out = __trans ;
           var &vlist ;
        run ;
 
        proc sql noprint ;
           select _name_ into :dlist separated by ' '
             from __trans
             where col1 >= &nobs
           ;
        quit ;
 
        %if ( &dlist ^= ) %then
        %do ;
 
           %put NOTE: DROPVAR removed the following variables from &data ;
           %put %str(     ) &dlist ;
           %put %str(     ) to create &out ;
 
           data &out ( drop = &dlist ) ;
              set &data ;
           run ;
 
        %end ;
        %else
        %if &out ^= &data %then
        %do ;
 
           data &out ;
              set &data ;
           run ;
 
        %end ;
 
    %mexit:
    %mend  dropvar ;
 
    Ian Whitlock