BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
ilikesas
Barite | Level 11

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

9 REPLIES 9
SASKiwi
PROC Star

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
Barite | Level 11

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
Barite | Level 11

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
Opal | Level 21

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
Barite | Level 11

Thanks Patrick, will come in handy!

SASKiwi
PROC Star

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.

ilikesas
Barite | Level 11

Hi KSharp,

 

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

Ksharp
Super User

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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 22785 views
  • 5 likes
  • 4 in conversation