topic Re: Delete empty columns in SAS Programming

Delete empty columns

aaou — Thu, 23 Jun 2016 23:55:58 GMT

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!

Re: Delete empty columns

Ksharp — Fri, 24 Jun 2016 01:32:27 GMT

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;

Re: Delete empty columns

aaou — Sat, 25 Jun 2016 16:11:11 GMT

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

Re: Delete empty columns

Ksharp — Sun, 26 Jun 2016 04:35:49 GMT


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

Re: Delete empty columns

LinusH — Sun, 26 Jun 2016 09:16:51 GMT

Instead of worry about empty columns, why not just keep the ones that you actually need?

Re: Delete empty columns

aaou — Tue, 28 Jun 2016 01:58:21 GMT

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?

Re: Delete empty columns

LinusH — Tue, 28 Jun 2016 06:14:27 GMT

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.

Re: Delete empty columns

rob9999 — Thu, 09 Jul 2020 04:58:09 GMT

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;