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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
Tom
Super User Tom
Super User

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;

aarony
Obsidian | Level 7

wow tom. you are a magician!

art297
Opal | Level 21

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;

Tom
Super User Tom
Super User

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.

aarony
Obsidian | Level 7

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?

naveen_srini
Quartz | Level 8

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;

  

Tom
Super User Tom
Super User

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

.

.

ChrisNZ
Tourmaline | Level 20

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 39030 views
  • 15 likes
  • 5 in conversation