DATA Step, Macro, Functions and more

Detect and delete null columns

Posts: 45

Detect and delete null columns



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

Posts: 4,245

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



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;

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

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

%mend drop_missing_vars;

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


Here more on FREQ and nlevels option:


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


Hope it helps.


Daniel Santos @



Super User
Posts: 23,774

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
  • 4 in conversation