topic Deleting Variables with Only Missing Values from Dataset with 4000+ Variables in New SAS User

Deleting Variables with Only Missing Values from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 18:04:57 GMT

Hello everyone and thank you in advance for your help! This is my first post, so please forgive me if this is confusing or redundant.

Each month, I receive a large dataset with around 4000+ variables and 3000+ observations. The data is from 30 different centers, but I am only interested in the data from 3 centers. Some variables are the same for all centers, but variables are only linked to some of the centers. I would like to get rid of the variables that have no values for the centers that I am interested in. I currently have a code that works, but I have to split my file into 6 lists of variables. I feel like there must be a way to do this in one step without getting the messae "ERROR: The text expression length (65639) exceeds maximum length (65534). The text expression has been truncated to 65534" because I'm SURE people work with even larger datasets than mine!

I have this current setup:

/* To split up my dataset, I am using this code 
to find the names of the variables so I 
can create a list. The SAS Macro is able to
 handle only 800 variable names at a time. 
Otherwise I receive an error about macro 
variable size and truncation*/ 

proc contents data= &month noprint out=_contents_ order=varnum;
run;

proc sort data=_contents_;
by varnum; 
run; quit; 

proc print data=_contents_ ;
where varnum in (1, 2, 800, 801, 1600, 1601, 2400, 2401, 3200, 3201, 4000, 4386);
var name varnum;
run;

/*I change the LET statement to reflect the part of the list I am at */ 

%LET listnum0=CallReportNum ReportVersion--Zip_Code_Information___Did_you_a; 
%LET set1= iteration1; %LET set0= &month; 

/*this is the main part of the code that 
gets rid of the variables with no observations */

proc transpose data= &set0(obs=0) out= vname ;
 var &listnum0;
run;

proc contents data= vname order= varnum; 
run; quit; 

proc sql;
 select catx(' ','n(',_name_,') as ',_name_) into : list separated by ',' from vname;
 create table temp as
  select &list from &set0
;quit;

proc transpose data= temp out= drop ;
 var _all_;
run;

proc sql;
 select _name_ into : drop separated by ' ' from drop where col1= 0;
quit;

data &set1; 
 set &set0 (drop= &drop);
run;

Is there a simple fix or creative workaround to this issue that I am unaware of?

Thanks again!

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

MarkWik — Wed, 12 Sep 2018 17:34:11 GMT

@sabotaged Welcome to SAS forum. Would be nice to follow if you could state your objective/requirement before your code like

My objective is to ...............

The following code does it............

The problem I am encountering....

Any solutions plz....................

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Reeza — Wed, 12 Sep 2018 17:35:27 GMT

Are the missing variables likely to be character or numeric?

This is actually asked pretty frequently, so a search generates several ready made solutions:

User group paper:

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

Previous question here:

https://communities.sas.com/t5/SAS-Procedures/Getting-rid-of-all-variables-for-which-there-are-ONLY-missing/td-p/172713

SAS Note:

http://support.sas.com/kb/24/622.html

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Tom — Wed, 12 Sep 2018 17:44:43 GMT

Observation is the word used for one record (or row) in a SAS dataset. So every variable in a dataset has the same number of observations.

Do you mean that you want find out which variables have only missing values?

Do you have any numeric variables? If so does it matter if the variable has some observations with special missing values, like .A or .Z?

Also why do you want eliminate them?

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 17:45:18 GMT

Hi, thanks so much for your reply. To answer your first question, there are both character and numeric variables, and there are a fair amount of both types dispersed throughout the document. As for the links you referenced, thank you so much, but I actually already looked through these (and many other) solutions before posting. I tried to use the %dropmiss macro last week, but was unable to successfully use it. After searching a lot of forum Q&A, I found a version of the main code that I posted in my original question. It works, but I have to split my dataset into 6 lists and run the code until I get through all the variables. I'm just wondering if there's any way to get around the message "ERROR: The text expression length (65639) exceeds maximum length (65534). The text expression has been truncated to 65534". I also researched about this error, but wasn't able to find a true workaround. I tried to change the size of the macro variable, but that didn't work either. Thanks again for your help! I apologize if I'm missing something that is otherwise obvious!

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 17:46:22 GMT

Hi, thank you for your reply! I'll try to edit my post to make it clearer.

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Tom — Wed, 12 Sep 2018 17:57:51 GMT

The limitation of that approach is the requirement to use macro variables. So don't use macro variables. It is probably easier to just generate code a text file instead.

* Get list of variables ;
proc transpose data=&month (obs=0) out=names ;
   var _all_;
run;

* Generate code to count non-missing values ;
filename code temp;
data _null_;
   file code ;
   set names end=eof;
   if _n_=1 then put
 'proc sql noprint;'
/'create table _counts as select' 
/' ' @
  ; else put ',' @ ;
  put 'sum(not missing(' _name_ ')) as ' _name_ ;
  if eof then put
 'from &month'
/';'
/'quit;'
  ;
run;

* Run generated code ;
%include code / source2 ;

* Generate DROP statement ;
filename code temp;
data _null_;
  set _counts ;
  array c _numeric_;
  file code lrecl=80 ;
  length _name_ $32 ;  
  put 'drop ' @;
  do _n_=1 to dim(c);
   if c(_n_)=0 then do ;
     _name_ = vname(c(_n_));
     put _name_ @ ;
  end;
  end;
  put ';' ;
run;

* Make version of data without empty variables ;
data &set1 ;
  set &month ;
  %include code / source2;
run;

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 17:58:42 GMT

Hi Tom, thanks for the reply. To answer your questions: 1. Yes, I would like to delete variables with only missing values. Sorry for the misuse of terminology! I will edit my post to reflect the correct term. 2. I have both numeric and character variables in my dataset. I'm just interested in getting rid of variables with all missing values. I don't have any special missing values, I think. 3. Each month, I receive a dataset that contains variables and records from 30 different centers. There are both character and numeric variables. I am only interested in the variables from 3 centers, so I'd like to delete the rest of the variables that are linked to other centers. After going through the process of cleaning the dataset, I end up with around 300 variables from the original 4000+ variables.

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Tom — Wed, 12 Sep 2018 18:01:54 GMT

Sounds like missing status has nothing to do with the request. Perhaps I misunderstood your original code.

So you just want to keep variables related to the selected centers?

How do you know which variables are related to which center?

How do you know which centers you want to keep?

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 18:13:35 GMT

Hi Tom, I wish I could upload the original Excel file, but it has patient information in it. There are 4000+ variables, but many of the variables don't have any data in them for any center. The rest of the variables have information in them, but only for certain centers. I don't have any way of telling which variables are related to which center, so that's why it would really help to get rid of variables with no values. I know which centers I want to keep because we are interested in the data from only 3 specific centers.

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Reeza — Wed, 12 Sep 2018 18:17:13 GMT

It does not need to be real data. Make fake data that has the same structure and issues as the real data. Yes this takes work but you'll get an answer much faster so in the long run you save time.

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Tom — Wed, 12 Sep 2018 18:19:31 GMT

Also just make data with a few columns. We don't need 4000 columns to generate test code.

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

Tom — Wed, 12 Sep 2018 18:24:38 GMT

Do you have a CENTER variable you can use to find the centers you want to keep?

If so you could just add a first step to subset the observations (rows) to just the centers of interest.

data step1 ;
  set &month ;
  where center in (......);
run;

Then use that dataset as the input to the code I posted and it will eliminate the columns that are all empty for just that subset of the rows.

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 18:34:49 GMT

Hi Tom, I just noticed the code you posted earlier! I was trying to make the process faster by using the "LET" statement, but that might be what is causing the issue. Yes, I do have a "center" variable, and the first thing I do is write a data step that keeps only the observations from those centers. Anyhow, I will try and incorporate the code that you posted now. Thank you very much!

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 18:36:18 GMT

Hi Reeza, I will try and do that next time for easier visualization. Thank you for the tip!

Re: Deleting Variables with Only Missing Values from Dataset with 4000+ Variables

ballardw — Wed, 12 Sep 2018 18:49:27 GMT

How do you bring the data set into SAS?

If the file structure does not change then using a data step with appropriate IF statement to keep the records for the centers you are interested in and a KEEP or DROP statement to only have the variables you want might be the best approach in the long run. It sounds like you already know which variables you want to keep so should not be too hard.

I have a project that shares mostly common variables with some other organizations which have a few fields my data does not need. DROP statement takes care of that just fine.

Re: Deleting Variables with Only Missing Values from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 18:55:24 GMT

I use proc import to bring in an Excel file. Unfortunately, the data structure changes each month! They keep adding/deleting variables, so I have no idea of knowing what to look for each time. There are a few core variables that are supposed to always stay the same, but they change the name of these variables sometimes. For example, in January, they named the gender column as "Gender" and in February they decided to change it to "Sex" and in March it became "GenderOfPatient". We have asked the centers to stay consistent, but not all of them listen. 😞

Re: Deleting Variables with Only Missing Values from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 18:59:24 GMT

Re: Deleting Variables with No Observations from Dataset with 4000+ Variables

sabataged — Wed, 12 Sep 2018 19:50:26 GMT

Tom, you are a genius! Thank you SO much for your help! This is exactly what I needed. Sending you many, many positive vibes. 🙂

Re: Deleting Variables with Only Missing Values from Dataset with 4000+ Variables

ballardw — Wed, 12 Sep 2018 21:57:51 GMT

@sabataged wrote:
I use proc import to bring in an Excel file. Unfortunately, the data structure changes each month! They keep adding/deleting variables, so I have no idea of knowing what to look for each time. There are a few core variables that are supposed to always stay the same, but they change the name of these variables sometimes. For example, in January, they named the gender column as "Gender" and in February they decided to change it to "Sex" and in March it became "GenderOfPatient". We have asked the centers to stay consistent, but not all of them listen. 😞

This sounds like it might be a case of getting management involved.

Any time data is to be shared between organizations, even parts of a single organization, it helps everyone if an actual formal agreement is made as to content, order and values of variables is done. That way you head off these headaches and can develop a process that is 1) reliable and 2) likely to save time and money.

I used to work for a company that did some contract work involving another companies data. When I got involved with that project I had to respond to the clients question about why there was a programming charge almost every month. When I explained that we had been receiving the data in different column order and that we had to modify the data to work for the project the client realized his budget was negatively affected because someone in his organization had not developed a standard approach. They fixed that and cut the cost of the project by about 5%. (And my programmer thanked me for not having to deal with the stupidity involved).

Document how much time it takes to fix this one time. Get a cost estimate from that time. Go to management with that information and frequency and you may see movement quickly.

Relying on the combination of Excel and Proc Import in production work is a recipe for multiple headaches.

Since proc import has to guess as to the field size and type after examining a very few rows of data you can have variables change lengths and type from month to month. Which means when those periodic data sets get combined for any reason you can have issues with 1) not combining at all due to errors if a field changes from character to numeric and 2) loss of data for character values due to varying lengths.

Save to CSV, use a data step to read data is much more predictable.

And in your case, the selection of data for specific centers and variables would stay the same.

I deal with anywhere from 10 to 30 Excel files per month. I save them to CSV format and use data steps to import them so the variable names, types, lengths and formats stay the same. That way when I combine the data across sites/ organizations I don't have issues related to different lengths and the process goes pretty smooth.

Plus if a field is supposed to be numeric and some idiot has entered text in the Excel file I get invalid data notices in the log I can use to back track to a source if needed.