01-13-2017 11:13 AM
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
01-13-2017 11:41 AM
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
01-13-2017 12:06 PM
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
01-13-2017 02:03 PM
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.