topic Re: How to drop a series of variables? in SAS Procedures

How to drop a series of variables?

Ken_oy — Thu, 27 Jun 2013 14:50:50 GMT

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!

Re: How to drop a series of variables?

Reeza — Thu, 27 Jun 2013 14:56:56 GMT

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.

Re: How to drop a series of variables?

Keith — Thu, 27 Jun 2013 15:09:22 GMT

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;

Re: How to drop a series of variables?

Scott_Mitchell — Mon, 08 Jul 2013 05:10:09 GMT

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;

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