BookmarkSubscribeRSS Feed
LukaszStasiak
SAS Employee

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

8 REPLIES 8
JohnHoughton
Quartz | Level 8
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
Quartz | Level 8

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

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
Opal | Level 21

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
Ksharp
Super User
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
Fluorite | Level 6 SJN
Fluorite | Level 6

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
Quartz | Level 8

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
Quartz | Level 8

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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 8 replies
  • 7674 views
  • 5 likes
  • 7 in conversation