deleting empty columns

[ilikesas]

Hi,

Sometimes I work with Excel files that contain empty columns, and when I import such files into SAS I get empty columns in the SAS table as well (SAS calls them F2, F13 etc depending on the empty columns' locations amidst the other non-empty columns).

Is it possible to automatically delete these empty columns or to not import them at all?

Thank you! 

[SASKiwi]

Welcome to Excel anarchy where it will let you do most anything.....

Try deleting the "Fnnn" columns in Excel just to the right of your named columns before you import. The same thing can happen with rows so do the same with those. 

[ilikesas]

Hi SASKiwi,

the thing is that I might have dozens and even hundreds of columns and the empty columns can be hidden pretty much randomly, and I might have to import many such Excel files, so it will be preferable to have some automatic way of deleting these empty columns rather than manual.

Thanks!

[ilikesas · Accepted solution]

Doing some research I found that such a question already exists in the SAS communities.

Here is a link:

https://communities.sas.com/t5/SAS-Procedures/delete-columns-if-they-are-empty/m-p/290221#U290221

The discussion is very interesting and instructive. I used KSharp's code in a macro and got the result that I wanted:

1) first I imported with proc import the data1-data10 that I needed.

2)) then I deleted the empty columns in these data tables:

%macro delete_empty;
%do i=1 %to 10;
ods select none;
ods output nlevels=temp;
proc freq data=data&i nlevels;
 tables _all_;
run;
proc sql;
 select tablevar into : drop separated by ','
  from temp
   where NNonMissLevels=0;
   
  alter table data&i
   drop &drop; 
quit;
%end;

%mend delete_empty;
%delete_empty

[Patrick]

If you just want to delete all columns in a SAS dataset where the name follows a pattern of "F<digits>" then below code would do.

%macro drop_col_Fdigit(table);

  %let table=%upcase(&table);
  %local drop_list;
  %let drop_list=;

  proc sql noprint;
    select name into :drop_list separated by ','
    from dictionary.columns
    where 
      libname="%scan(WORK.&table,-2)" 
      and memname="%scan(WORK.&table,-1)" 
      and prxmatch('/^F\d+\s*$/oi',name)>0
    ;
  quit;

  %if %bquote(&drop_list) ne %bquote() %then
    %do;
      proc sql noprint;
        alter table &table
        drop &drop_list
        ;
      quit;
    %end;

%mend;


data imported;
  set sashelp.class;
  f5=.;
  b5=.;
  _f5=.;
  F123=' ';
  F5F=.;
  '5F'n=.;
  '5F5'n=.;
run;

%drop_col_Fdigit(work.imported);

[ilikesas]

Thanks Patrick, will come in handy!

[SASKiwi]

Personal opinion: if you have to continually clean up what others have done in spreadsheets you have to deal with then I'd be worried about what else is happening in the populated sections. From personal experience I've had users change column headings, column locations, column types, data order - the list goes on and on - without realising the consequences of what they are doing.

At the end of the day if you want reliable and robust data inputs from spreadsheets, then having a defined and agreed layout and an agreement to not change it without consultation is pretty much essential....end of rant.

[Ksharp]

https://communities.sas.com/t5/SAS-Procedures/delete-columns-if-they-are-empty/m-p/290365#M59735

[ilikesas]

Hi KSharp,

thanks for the code! I would have accepted it as the solution if it was posted in my question!

[Ksharp]

data have;
 set sashelp.class;
 call missing(name,age,weight);
run;

proc iml;
use have nobs nobs;
read all var _char_ into x[c=varname1];
read all var _num_ into y[c=varname2];
close;
miss1=varname1[loc(countmiss(x,'col')=nobs)];
miss2=varname2[loc(countmiss(y,'col')=nobs)];

submit miss1 miss2;
 data want;
  set have(drop=&miss1 &miss2);
 run;
endsubmit;
quit;