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.
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;
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;
wow tom. you are a magician!
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;
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.
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?
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;
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')
.
.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.