How to drop a series of variables?

[Ken_oy]

Hi everyone,

I have a large dataset which contains more than 150 variables.

I want to drop some of those variables which have a "flag" in the variable name, for example: Q15_flag.

I try to use (:), like drop :flag. but it did not work that way. Any help?

Thanks!

[Reeza]

There isn't a quick way to reference variables with suffixes, a better naming convention would be flag_q15, flag_q12 etc.

Then you could use flag:

Two alternatives,

1) change your naming convention in previous step

2) create a macro variable that has all the variable names that end in _flag an use that in your drop statement.

[Keith · Accepted solution]

I would create a macro list from the dictionary columns.

proc sql noprint;

select name into :vars separated by  ' '

from dictionary.columns

where upper(libname)='<ENTER>'

  and upper(memname)='<ENTER>'

  and upper(name) like '%FLAG';

quit;

data want;

set have (drop=&vars.;);

run;

[Scott_Mitchell]

I offer the following solution purely because my previous manager hated us using SQL - I think he just couldn't read it to be honest - so we had to come up with alternate solutions.

%macro test(dsn,suffix);                                                                                                              

%global varlist;                                                                                                                      

/*This section of code opens the data sets and determines the number of variables*/                                                   

%let dsid=%sysfunc(open(&dsn));                                                                                                       

%let nvars=%sysfunc(attrn(&dsid,nvars));                                                                                              

%let varlist=;                                                                                                                        

/*This section of code checks each variable name to see if the target suffix is found.                                                

  If it is, then the variable name is added to VARLIST*/                                                                              

%do i=1 %to &nvars;                                                                                                                   

  %let name=%sysfunc(varname(&dsid,&i));                                                                                              

  %let chk=%sysfunc(reverse(&name));                                                                                                  

  %if %length(&name)>=%length(&suffix) %then %do;                                                                                     

    %if %sysfunc(reverse(%substr(&chk,1,%length(&suffix))))=&suffix %then %let varlist=&varlist &name;                                

  %end;                                                                                                                               

%end;                                                                                                                                 

/*The following line of code closes the data set*/                                                                                    

%let rc=%sysfunc(close(&dsid));                                                                                                       

%put &varlist;                                                                                                                     

data &dsn.;

  set &dsn. (drop = &varlist);

run;

%mend;         

%test(a,_flag);

Obviously this isn't as simple as using the SQL method, however it does produce a suitable outcome given the constraints imposed upon me.

Regards,

Scott