How to detect all empty columns in a dataset and delete\drop them?

Question

As suggested in the title, I'd like to drop all empty columns\variables(where all records are empty or equal null or ""), so as to reduce time cost in later execution.

Detailed scenario:

I have a dataset() with 1000 columns, some\lots of which are empty. Now I want to create a new dataset in which I need to add columns under some conditions of previous dataset.

data new;

   set old;

   if oldcol1 ne "" then newcol1='<a>'||strip(oldcol1)||'</a>';

   end;

   if oldcol2 ne "" then newcol2='<a>'||strip(oldcol2)||'</a>';

   end;

   ...

   ...;

   drop oldcol1 oldcol2.....oldcol1000;
   run;

It takes quite some time to execute given the following reason:

1. number of old columns is huge

2. in fact I need to do a loop in another dataset to set the number after oldcol

ColNumber

 1

 2

 3

...

1000

So you can imagine how many times to be executed in terms of searching, finding and setting values.

Hence one way I could think of to reduce time cost is drop all empty columns first. But any inputs regarding optimizing the algorithm is highly welcomed as well.

Thanks

Solution 1

Here's a generic macro that you can use to generate a list of the empty columns in the source data set, which you can then pass to a drop statement. It uses proc format and proc freq so it is relatively fast.

%macro findmiss(ds,macvar);
%local noteopt;
%let noteopt=%sysfunc(getoption(notes));
option nonotes;
*ds is the data set to parse for missing values;
*macvar is the macro variable that will store the list of empty columns;
%global &macvar; 
proc format;
  value nmis  .-.z =' ' other='1';
  value $nmis ' '=' ' other='1';
run;
ods listing close;
ods output OneWayFreqs=OneValue(
  where=(frequency=cumfrequency 
  AND CumPercent=100));

proc freq data=&ds;
  table _All_ / Missing ;
  format _numeric_ nmis. 
        _character_ $nmis.;
  run;
ods listing;
data missing(keep=var);
  length var $32.;
  set OneValue end=eof;
    if percent eq 100 AND sum(of F_:) < 1 ;
    var = scan(Table,-1,' ');
run;
proc sql noprint;
  select var into: &macvar separated by " "
  from missing;quit;
option &noteopt.;
%mend;

Here is how you might use it:

%findmiss(old,droplist); /*generate the list of empty columns */
data new;
  set old(drop=&droplist);
run;

Solution 2

I agree that proc transpose is a good idea:

proc transpose data=old out=temp; 
var _ALL_;
run;

data _NULL_;
set temp end=eof;
    array cols {*} COL: ;
do i = 1 to dim(cols);
    cols[i]=ifn((strip(cols[i])=" " or strip(cols[i])="."),0,1);
end;
if sum(of COL:)=0 then 
call symput("dropvars", catx(" ",symget("dropvars"),_NAME_));
run;

    data new; set old (drop=&dropvars); run;

Comments

[Lauren Samuels]

See also this question

[mj023119]

Thanks a lot, Johns. This is a great answer although I know little about the two proc mentioned here, which I will study later. BTW, can you pls let me know a little more about your idea or thoughts so that I can understand better?

[user1774301]

@Jackie: proc format allows you to create custom formats. In this case we create a format that sets all non-missing values to 1. Formats don't actually change the underlying data - just the way it is viewed. The freq (frequency) procedure counts up the number of each level of the formatted values (in this case 1's and missings). From there we have some data cleaning - followed by a call to proc sql to store the variables in a macro variable that can be used later. A slightly different technique using the levels statement in proc freq is shown in the link @Louisa provided.