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

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

LukaszStasiak — Thu, 30 Jun 2016 10:01:30 GMT

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

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

JohnHoughton — Thu, 30 Jun 2016 10:48:15 GMT

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

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

JohnHoughton — Thu, 30 Jun 2016 11:15:05 GMT

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

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

Reeza — Thu, 30 Jun 2016 11:28:46 GMT

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.

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

PGStats — Thu, 30 Jun 2016 18:01:40 GMT

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

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

Ksharp — Fri, 01 Jul 2016 02:32:01 GMT

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;

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

SJN — Fri, 01 Jul 2016 11:48:30 GMT

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

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

JohnHoughton — Fri, 01 Jul 2016 12:13:40 GMT

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.

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

bnawrocki — Mon, 03 Apr 2023 15:47:53 GMT

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!