topic Re: want to drop variables with same suffix in SAS Programming

want to drop variables with same suffix

aarony — Sat, 14 Mar 2015 17:45:33 GMT

I have 800+ variables.

some of which have the same suffix _C, ie: alpha1_C alpha1 alpha2_C alpha2 and so on...

how do i remove the ones with _C?

many thanks in advance.

Re: want to drop variables with same suffix

Tom — Sat, 14 Mar 2015 18:22:23 GMT

You will need to query a list of the variables to generate the list of those that you need to drop.

proc contents data=have out=contents(keep=name) noprint ; run;

proc sql noprint ;

select name into :droplist separated by ' '

from contents

where upcase(name) like '%^_C' escape '^'

;

quit;

data want ;

set have (drop=&droplist);

run;

Re: want to drop variables with same suffix

aarony — Sat, 14 Mar 2015 18:26:29 GMT

wow tom. you are a magician!

Re: want to drop variables with same suffix

art297 — Sat, 14 Mar 2015 18:35:50 GMT

Tom and Aarony,

Just one suggestion! Why bother running both proc contents and proc sql? e.g.:

data have;

input a a_c b b_c c c_c;

cards;

1 2 3 4 5 6

6 5 4 3 2 1

;

proc sql noprint;

select name

into :drops separated by ' '

from dictionary.columns

where upcase(name) like '%^_C' escape '^' and

libname eq "WORK" and

memname eq "HAVE"

;

quit;

data want;

set have (drop=&drops.);

run;

Re: want to drop variables with same suffix

aarony — Sat, 14 Mar 2015 18:42:44 GMT

tom,

sorry. this is a dumb question but there are variables that ends with S.

ie, CFV011S

i just wnated to drop the variables that ends with S, but it is not working. do u have any color?

Re: want to drop variables with same suffix

naveen_srini — Sat, 14 Mar 2015 18:50:21 GMT

just create a macro variable at the top, and change to any letter you want in tom's code:

proc contents data=have out=contents(keep=name) noprint ; run;

%let change= C; /* change here to whatever alphabets you want*/

proc sql noprint ;

select name into :droplist separated by ' '

from contents

where upcase(name) like '%^_&change' escape '^'

;

quit;

data want ;

set have (drop=&droplist);

run;

Re: want to drop variables with same suffix

Tom — Sat, 14 Mar 2015 18:50:23 GMT

It makes the logic clearer to the novice user.

Also there are potential side effect to querying DICTIONARY tables. Probably not in this case since both the LIBNAME and MEMNAME values are exact matches.

Re: want to drop variables with same suffix

Tom — Sat, 14 Mar 2015 18:52:32 GMT

Your WHERE clause will be different in that case.

Instead of looking for variables that end in _C (where upcase(name) like '%^_C' escape '^')

you will want to look for variables that end in S (where upcase(name) like '%S')

Re: want to drop variables with same suffix

ChrisNZ — Wed, 22 Jul 2015 23:42:47 GMT

Apologies for reviving this thread, but this is a recurring question, and I just posted this on linkedin and thought it belongs here too.

A quick and dirty macro does the work without extra SAS code, and does more.

Unsure why I need the quotes, there. Probably obvious, but no time to think about it. If someone knows...

%macro var_filter(table, string, filter); %* filter can be: end, anywhere;

%let string=%upcase(&string);

%local dsid varnum varname diff;

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

%if &dsid=0 %then %return;

%do varnum=1 %to %sysfunc(attrn(&dsid,nvars));

%let varname=%upcase(%sysfunc(varname(&dsid,&varnum)));

%let diff=%eval(%length(&varname) - %length(&string));

%if &diff >= 0 %then %do;

%if %upcase(&filter)=END %then %if "%substr(&varname,&diff+1)" = "&string" %then &varname;

%if %upcase(&filter)=ANYWHERE %then %if %index(&varname,&string) %then &varname;

%end;

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

%mend;

data T;

set SASHELP.CLASS;

drop %var_filter(SASHELP.CLASS, GE, END);

run;