BookmarkSubscribeRSS Feed
aaou
Obsidian | Level 7

What's the easiest way to remove empty columns/variables from a data set? As in when I get a subset of data from my main dataset, if there are columns which are empty/null in that subset, how do I write a program to remove such variables from the subset?

 

Thanks!

7 REPLIES 7
Ksharp
Super User
data class;
 set sashelp.class;
 call missing(name,age);
run;



ods select none;
ods output nlevels=temp;
proc freq data=class nlevels;
tables _all_;
run;
ods select all;
proc sql noprint;
 select tablevar into : drop separated by ' '
  from temp 
   where NNonMissLevels=0;
quit;
data want;
 set class(drop=&drop);
run;
aaou
Obsidian | Level 7

Is there a way to do this without specifying the names of the variables? 

Ksharp
Super User

???  That was exactly what I am doing . No need any variable name, just run the code and gave you what you want .

LinusH
Tourmaline | Level 20
Instead of worry about empty columns, why not just keep the ones that you actually need?
Data never sleeps
aaou
Obsidian | Level 7

It's like this, these variables have values for certain years. But  are empty for some years. So when I want to extract a dataset for a particular year from the main dataset, I want in that dataset all the null columns not to appear. Could you advice me how to do this?

 

 

LinusH
Tourmaline | Level 20
1. Take control over your data. This could involve having a meta data table containing information of which variables appear what years. Then you could dynamically build a keep statement during you filtering. Or
2. Search these forums for a more generic solution. There have been multiple similar inquiries the last couple of months.
Data never sleeps
rob9999
Fluorite | Level 6

Hi, i stumbled upon this and many posts when i have the same issue. please find my solution below (it's a bit manual where you need to know where the blank columns are).

proc contents data=messyexcel order=varnum out=abcde (keep=name varnum) noprint ;
run;*this will generate a dataset with all your variables and their variable number. ;

 

proc sort data=abcde;
by varnum;
run;*sorting the output dataset by their variable number;


proc sql;*create a macro list to store all your variable names that you want to delete, in my case, anything beyond column 1186 are blanks;
select name into :deletelist separated by '  '
from abcde
where varnum>1186;
quit;

 

data better_data (drop=&deletelist);
set messyexcel;
run;

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 7 replies
  • 11752 views
  • 8 likes
  • 4 in conversation