Detect and delete null columns

[Planck]

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

[Rick_SAS]

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

[DanielSantos]

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

[Reeza]

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.