BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shixin
Calcite | Level 5

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*/



1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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.

data_null__
Jade | Level 19

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;


1-29-2015 11-58-34 AM.png

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 2947 views
  • 4 likes
  • 4 in conversation