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!
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;
Is there a way to do this without specifying the names of the variables?
??? That was exactly what I am doing . No need any variable name, just run the code and gave you what you want .
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?
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.