How To Detect All Empty Columns In A Dataset And Delete\drop Them?

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 sce

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 100AND sum(of F_:) < 1 ;
    var = scan(Table,-1,' ');
run;
proc sql noprint;
  selectvarinto: &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;
  setold(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 = 1to dim(cols);
    cols[i]=ifn((strip(cols[i])=" "orstrip(cols[i])="."),0,1);
end;
ifsum(of COL:)=0then 
call symput("dropvars", catx(" ",symget("dropvars"),_NAME_));
run;

    data new; setold (drop=&dropvars); run;

Solution 3:

Something like this?

data work.temp1;
  attrib idcol length=8;
  set work.old;

  idcol=_n_;
run;

proc transpose data=work.temp1 out=work.temp2 name=varname;
  var oldcol1-oldcol1000;
  by idcol;
run;

proc sql;
  createtable work.temp3 asselectdistinct varname from work.temp2 wherenot missing(col1);
quit;

data _null_;
  set work.temp3 end=lastrec;

  attrib nvarname length=$32;

  if _n_=1then do;
    callexecute('data work.new;');
    callexecute('set work.old;');
  end;

  nvarname ='newcol'|| strip(input(substr(varname,4),4.));
  callexecute('attrib '|| strip(nvarname) ||' length=$250;');

  callexecute(strip(nvarname) ||'= "<a>" || strip('|| strip(varname) ||') || "</a>";' );

  if lastrec then do;
    callexecute('drop oldcol1-oldcol1000;');
    callexecute('run;');
  end;
run;