DATA Step, Macro, Functions and more

Detect and delete null columns

Reply
Contributor
Posts: 45

Detect and delete null columns

Hi,

 

Everything is in the title.

 

Whatever the size of my SAS table is, whatever the number of columns is, is there a way to travel along every column to detect null colums, and to delete them?

 

What I mean by null column is only a column where all values are null, without exception.

 

I was looking for with PROC SQL but it seems very difficult...

 

Thanks for the help

SAS Super FREQ
Posts: 3,476

Re: Detect and delete null columns

This question is asked often. See the SAS Sample "Drop variables from a SAS data set whose values are all missing"

 

You can also search this forum or the internet by using queries such as

SAS delete variables all missing

Super Contributor
Posts: 474

Re: Detect and delete null columns

Hi.

 

One way to do this:

 

 

options mprint;

%macro drop_missing_vars(DSET);

* check missings vars;
ods select none;
ods output nlevels=_MISSINGVARS (where=(nmisslevels>0 and nnonmisslevels=0));
proc freq data=&DSET nlevels;
run;

* get missing vars names;
%let _MISSINGVARS=;
proc sql noprint;
select TABLEVAR into :_MISSINGVARS separated by ' ' from _MISSINGVARS;
quit;

* if there is any, drop;
%if %str(&_MISSINGVARS) ne %then %do;
data want;
set have (drop=&_MISSINGVARS);
run;
%end;

%mend drop_missing_vars;

%drop_missing_vars(have); * run macro providing the dataset name;

 

Here more on FREQ and nlevels option: http://support.sas.com/kb/30/867.html

 

The rest is just building a list into a macro var and afterthat drop the vars.

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

 

 

Super User
Posts: 17,819

Re: Detect and delete null columns

And just a note that SAS doesn't really have a NULL value, it has missing which can often be considered the same, but in certain cases and DBs they are not. 

Ask a Question
Discussion stats
  • 3 replies
  • 184 views
  • 0 likes
  • 4 in conversation