DATA Step, Macro, Functions and more

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

Reply
SAS Employee
Posts: 6

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

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

Contributor
Posts: 39

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

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
Contributor
Posts: 39

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

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

 

Super User
Posts: 17,801

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

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. 

Respected Advisor
Posts: 4,644

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

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

 

 

PG
Super User
Posts: 9,676

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

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;
Contributor SJN
Contributor
Posts: 21

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

[ Edited ]

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 ) ;

Contributor
Posts: 39

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

[ Edited ]

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.

Ask a Question
Discussion stats
  • 7 replies
  • 522 views
  • 4 likes
  • 6 in conversation