topic Re: Detect and delete null columns in SAS Programming

Detect and delete null columns

Planck — Fri, 13 Jan 2017 16:13:37 GMT

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

Re: Detect and delete null columns

Rick_SAS — Fri, 13 Jan 2017 16:41:13 GMT

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

Re: Detect and delete null columns

DanielSantos — Fri, 13 Jan 2017 17:06:32 GMT

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

Re: Detect and delete null columns

Reeza — Fri, 13 Jan 2017 19:03:31 GMT

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.