Drop variables with all-zero values from a SAS data set

Question

I often work with a large number of variables that have zero or empty values only, but I could not find a SAS command to drop these unwanted variables. I know we can use SAS/IML, but I encountered such cases many times and would like to have a macro that may help me without having to type the variable names to avoid errors. Here is my code for removing variables with zero values only. It works to produce a cleaned output data set y from a raw data set x without using the names of the variables. I hope others could have a better solution or help me to make mine better.

   %Macro dropZeroV(x, y) ;

        proc means data = &x. ; 
            var _numeric_;
            output out = sumTab ; run;
        proc transpose data = sumTab(drop = _TYPE_) out= sumt; var _Numeric_; id _STAT_; run;
        %let Vlst =;
        proc sql noprint;
            select _NAME_ into : dropLst separated by ' '
            from sumT
            where Max=0 and Min =0;
        data &y.;
         set &x.; drop &dropLst.;
        run;
    proc print data = &y.; run;
    %Mend dropZeroV;

Rando Rando · Accepted Answer · 2019-09-20T22:01:26

Use STACKODS and ODS SUMMARY to get the table in the format needed in one step rather than multiple steps. This limits it to the sum, since if the sum = 0, all values are 0. You may also want to look at rounding to avoid any issues with numeric precision.

PROC MEANS + PROC TRANSPOSE go to :

ods select none;
proc means data= &x. stackods sum;
var _numeric_;
ods output summary = sumT;
run;

Drop variables with all-zero values from a SAS data set

1 Answers