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
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
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.