Is there a way to select all non-empty values from table?

[LukaszStasiak]

Hello,

I have a table with >900 variables and whenever I want to check something on it and I don't have a list of variables that are of my interest, I use select(*) and have to scroll right and watch all the columns.

Is there an fast way to filter the table I am looking at so that for my conditions in where statement I am getting only those columns, that are not empty? In my dataset for about 900 variables about 2/3 of them are being empty for those queries I am looking.

I dont want to view only those columns I'm interested with, which would be the best idea but these queries require that I get different sets of variables every time so I just want to get rid of the empty ones.

Thanks,

Lukasz Stasiak

[JohnHoughton]

Are the variables all numeric? If yes, then I would start by using proc means or summary with the where condition to get a count of missing values for each variable. Then transpose & then use the _freq_ variable to calculate the percentage missing. Then use proc sql with a select ... into where pctmiss <100 clause to create a macro variable containing a variable list of variables that are not 100% missing. Then use that macro variable in a keep= statement to create a subset of the original dataset.

However , if the variables you are looking at are character, or a mixture of character & numeric then that won't work

[JohnHoughton]

I forgot about this %DROPMISS macro which I have used in the past - works on character & numeric variables.

http://support.sas.com/resources/papers/proceedings10/048-2010.pdf

[Reeza]

It depends if you have all missing or simply a lot missing. If all missing use @JohnHoughton solution. If it's a lot, consider switching your data structure to a long format. 

[PGStats]

SAS can work with all kinds of data, but humans cannot deal with 900+ dimensions. 

Normalize your data and your life will get simpler.

https://en.wikipedia.org/wiki/Database_normalization

[Ksharp]

data have;
 set sashelp.class;
 call missing(name,height);
run;



ods select none;
ods output nlevels=nlevels;
proc freq data=have nlevels;
table _all_;
run;
ods select all;

proc sql noprint;
 select tablevar into : drop separated by ','
  from nlevels
   where NNonMissLevels=0;
 alter table have
  drop &drop;
quit;

[SJN]

Hi, Caould you explain the data step.

/* Code*/

data _null_ ;
set _cntnts_ end=lastobs nobs=nobs ;
SAS Global Forum 2010 Coders' Corner
4
if nobs = 0 then stop ;
n_char + ( type = 2 ) ;
n_num + ( type = 1 ) ;
/* create macro vars containing final # of char, numeric variables */
if lastobs
then do ;
call symput( 'N_CHAR', left( put( n_char, 5. ))) ;
call symput( 'N_NUM' , left( put( n_num , 5. ))) ;
end ;
run ;

Whats the below logic do?

n_char + ( type = 2 ) ;
n_num + ( type = 1 ) ;

[JohnHoughton]

It's creating a count of the number of character and numeric variables based on the value of the type variable in the proc contents output.

[bnawrocki]

I know this is a really old thread, but I finally have a use for this - I've got a SAS dataset with hundreds of hospital diagnosis codes, and I only want to keep the code fields that have at least one non-blank value (to save space). I was going to use Sridharma's %DROPMISS macro mentioned above (it's hundreds of lines of SAS code, but it works well). However, this option using the NLEVELS option (which I've never heard of) also works well, and it's MUCH shorter. I think I'll use that. Thanks!