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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: