Help using Base SAS procedures

delete columns if they are empty

Reply
Contributor
Posts: 22

delete columns if they are empty

Hi ,

I used proc import and get datasets a and b

 

 

PROC IMPORT OUT= WORK.auto&i DATAFILE= "C:\auto\&&price&i.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="auto"; 
     GETNAMES=YES;
RUN;

 

and i get two datasets as below  and how to delete the columns which are empty like C,D,E,F,G,H from auto1and column D in auto2

 

 

Auto1

 

ZonepricingCDEFGH
8762311428      
6501231556      
7542581235      

 

 

Auto2

SafetymeasuresdimensionD
floor12 
lock0.953 
alarm0.855 
alarm0.987 

 

 

Super User
Posts: 11,343

Re: delete columns if they are empty

Welcome to the wonderful word of Excel and poor data results.

You could drop the variables for any purpose. Or if you really want a new data set:

 

data work.want;

    set work.auto1 (drop=C D E F G H);

run;

Contributor
Posts: 22

Re: delete columns if they are empty

Hi ,

I am running in a loop if i drop C column when i running i a loop i will not have C column in Auto2 also right but i want that column in Auto2

 

I need to write code n0284330in such a way that it drops the empty columns automatically from each dataset while creating the output

 

so that the same code used for  Auto1 it will drop CD EFGH

and for Auto2 it will drop D

 

 

Can anyone pls help

 

Thanks

Super User
Posts: 11,343

Re: delete columns if they are empty

If one really wants to automate a process then using Excel as a data source and Proc import to read the data are two suboptimal choices.

Excel in the form of XLSX or XLS files has no actual structure and manipulation of files can creat "phantom" variables and rows of data as you are experiencing.

Proc Import has to guess every single time a file is read as to the types of data and characteristics. Using default settings for proc import and Excel spreadsheets you can get different data types just by changing the sort order of the data before proc import.

 

If these files are supposed to contain the same data it may be worth the effort to convert the XLSX to CSV and write, or modify the program creatd by proc import for one file, a custom program to read in a consistent manner.

Super User
Posts: 5,424

Re: delete columns if they are empty

There are at least 10 threads on communities that deals with this issue. Just do a search.
Data never sleeps
Super User
Posts: 10,020

Re: delete columns if they are empty

The simplest way is using proc freq+nlevels, if you want more flexibilty ,try SQL.

data have;
 set sashelp.class;
 call missing(sex,age);
 if _n_=3 then call missing(weight);
 run;
 
ods select none;
ods output nlevels=temp;
proc freq data=have nlevels;
 tables _all_;
run;
proc sql;
 select tablevar into : drop separated by ','
  from temp
   where NNonMissLevels=0;
   
  alter table have
   drop &drop; 
quit;
 

Super User
Posts: 7,762

Re: delete columns if they are empty

[ Edited ]

If you use a textual file format and a data step for the transfer, the data step specifies the columns. Any Excel-typical "extras" are automatically dropped.

Proc Import is nice for tests, but should never be used in production-stage programs.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 6 replies
  • 842 views
  • 13 likes
  • 5 in conversation