topic Re: Dropping a range of variables in drop statement in SAS Procedures

Dropping a range of variables in drop statement

shixin — Thu, 29 Jan 2015 15:53:35 GMT

Hi all,

I have a dataset with ~100 variables and I want to drop 30 of them. Those 30 happen to have similar variable names (eg XXX_flag).

I want to find a way to drop those 30 variables highlighted in red below and I tried using (drop= :_flag) but it doesn't work.

Is there a shorter way to drop all those flags?

Below are examples of my variables:

Name

Address

Member

State

City

Zipcode

Country

.... /*Many many other demographics variables*/

Apple_flag

Banana_flag

Pear_flag

Orange_flag

Coconut_flag

..../*Many many other _flags*/

Apple_sum

Banana_sum

Pear_sum

..../*Many other _sum*/

Re: Dropping a range of variables in drop statement

ballardw — Thu, 29 Jan 2015 16:22:49 GMT

A macro from the SUGI 30 paper 029-30 Text Utility Macros for Manipulating Lists of Variable Names by Robert J Morris might help:

%macro parallel_join(words1, words2, joinstr, delim1=%str( ), delim2=%str( ));
%local i num_words1 num_words2 word outstr;
%* Verify macro arguments. ;

%if (%length(&words1) eq 0) %then %do;
%put ***ERROR(parallel_join): Required argument 'words1' is missing.;
%goto exit;
%end;
%if (%length(&words2) eq 0) %then %do;
%put ***ERROR(parallel_join): Required argument 'words2' is missing.;
%goto exit;
%end;
%if (%length(&joinstr) eq 0) %then %do;
%put ***ERROR(parallel_join): Required argument 'joinstr' is missing.;
%goto exit;
%end;
%* Find the number of words in each list. ;
%let num_words1 = %num_tokens(&words1, delim=&delim1);
%let num_words2 = %num_tokens(&words2, delim=&delim2);
%* Check the number of words. ;
%if (&num_words1 ne &num_words2) %then %do;
%put ***ERROR(parallel_join): The number of words in 'words1' and;
%put *** 'words2' must be equal.;
%goto exit;
%end;
%* Build the outstr by looping through the corresponding words and joining
* them by the joinstr. ;
%let outstr=;
%do i = 1 %to &num_words1;
%let word = %scan(&words1, &i, &delim1);
%let outstr = &outstr &word&joinstr%scan(&words2, &i, &delim2);
%end;
%* Output the list of joined words. ;
&outstr
%exit:
%mend parallel_join;

use in your drop statement

(drop = %parallel_join(apple pear banana , _flag ) )

since you still need to provide the stems I'm not sure it'll help much from copy and paste unless you are also processing other suffixes at the same time such as _sum _max _min and such.

Re: Dropping a range of variables in drop statement

RW9 — Thu, 29 Jan 2015 16:25:50 GMT

Hi,

PLease use the search functionality, I posted something about this some days back and there are many examples, e.g.:

https://communities.sas.com/message/234505#234505

Query the SAS metadata in sashelp.vcolumn, generate a macro variable then drop those on the macro variable.

Re: Dropping a range of variables in drop statement

data_null__ — Thu, 29 Jan 2015 18:00:28 GMT

How about a modern approach using DOSUBL?

data test;
   length 
      Name
      Address
      Member
      State
      City
      Zipcode
      Country $8
      /*Many many other demographics variables*/
      Apple_flag
      Banana_flag
      Pear_flag
      Orange_flag
      Coconut_flag 8
      /*Many many other _flags*/
      Apple_sum
      Banana_sum
      Pear_sum 8
   ;

   stop; 
   call missing(of _all_);
   run; 

%macro
   expand_varlist /*Returns an expanded variable list and optionally creates an indexed data set of the variable names*/
      (
         data    = _LAST_, /*Input data*/
         var     = _ALL_,  /*Variable List expanded*/
         where   = 1,      /*Where clause to subset OUT=, useful for selecting by a name suffix e.g. where=_name_ like '%_Status'*/
         outexpr = _NAME_, /*An expression that can be used to modify the names in the expanded list*/
         keep    = ,       /*Keep data set option for DATA=*/
         drop    = ,       /*Drop data set option for DATA=*/
         out     = ,       /*Output data indexed by _NAME_ and _OBS*/
         name    = _NAME_, /*Name of the variable name variable in the output data set*/
         label   = _LABEL_,/*Name of the variable name label variable in the output data set*/
         obs     = _OBS_,  /*Name of the variable index variable in the output data set*/
         dlm     = ' '     /*List delimiter*/
      ); 
   %if %sysevalF(&sysver lt 9.3,boolean) %then %do; 
      %put NOTE: Macro &sysmacroname requires SAS version 9.3 or higher;
      %return; 
      %end; 
   %local rc returned code1 code2 code3 code4;
   %if %superq(out) ne %then %let code3 = %str(data &out(index=(&obs &name)); set &out; &obs+1; run;); 
   %else %do; 
      %let out=%str(work._deleteme_); 
      %let code3 = %str(proc delete data=work._deleteme_; run;); 
      %end; 
   %let code1 = %str(options notes=0; proc transpose name=&name label=&label data=&data(obs=0 keep=&keep drop=&drop) out=&out(where=(&where)); var &var; run;); 
   %let code2 = %str(proc sql noprint; select &outexpr into :returned separated by &dlm from &out; quit;); 
   %let code4 = %str(options notes=1;); 
   %let rc=%sysfunc(dosubl(&code1 &code2 &code3 &code4));
   %put NOTE: Macro(&sysmacroname) retured: %qsysfunc(quote(%superq(returned)));
&returned.
   %mend expand_varlist; 


/*%put %expand_varlist(data=test,where=(upcase(_name_) like '%_FLAG'));*/

data noflag;
   set test(drop=%expand_varlist(data=test,where=(upcase(_name_) like '%_FLAG')));
   run; 

proc contents varnum; 
   run;