want to drop variables with same suffix

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

want to drop variables with same suffix

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.


Accepted Solutions
Solution
‎03-14-2015 02:22 PM
Super User
Super User
Posts: 6,364

Re: want to drop variables with same suffix

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;

View solution in original post


All Replies
Solution
‎03-14-2015 02:22 PM
Super User
Super User
Posts: 6,364

Re: want to drop variables with same suffix

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;

Frequent Contributor
Posts: 102

Re: want to drop variables with same suffix

wow tom. you are a magician!

Esteemed Advisor
Posts: 7,296

Re: want to drop variables with same suffix

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;

Super User
Super User
Posts: 6,364

Re: want to drop variables with same suffix

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.

Frequent Contributor
Posts: 102

Re: want to drop variables with same suffix

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?

Frequent Contributor
Posts: 115

Re: want to drop variables with same suffix

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;

  

Super User
Super User
Posts: 6,364

Re: want to drop variables with same suffix

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')

.

.

Trusted Advisor
Posts: 1,510

Re: want to drop variables with same suffix

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;

  %end;

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

%mend;

data T;

  set SASHELP.CLASS;

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

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 8596 views
  • 9 likes
  • 5 in conversation