BookmarkSubscribeRSS Feed
Planck
Obsidian | Level 7

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

3 REPLIES 3
Rick_SAS
SAS Super FREQ

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
Barite | Level 11

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
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 16408 views
  • 0 likes
  • 4 in conversation