DATA Step, Macro, Functions and more

deleting empty columns

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

deleting empty columns

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! 


Accepted Solutions
Solution
‎10-29-2016 12:29 AM
Super Contributor
Posts: 441

Re: deleting empty columns

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

 

View solution in original post


All Replies
Super User
Posts: 3,260

Re: deleting empty columns

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. 

Super Contributor
Posts: 441

Re: deleting empty columns

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!

Solution
‎10-29-2016 12:29 AM
Super Contributor
Posts: 441

Re: deleting empty columns

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

 

Respected Advisor
Posts: 4,173

Re: deleting empty columns

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);
Super Contributor
Posts: 441

Re: deleting empty columns

Thanks Patrick, will come in handy!

Super User
Posts: 3,260

Re: deleting empty columns

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.

Super User
Posts: 10,044
Super Contributor
Posts: 441

Re: deleting empty columns

Hi KSharp,

 

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

Super User
Posts: 10,044

Re: deleting empty columns


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;


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1693 views
  • 3 likes
  • 4 in conversation