I have a dataset where all the variables end in "_c" and I'd like to now remove the suffix.
However, I can't seem to find a way to do so. I found some code below using the scan feature, but it doesn't seem to work.
Does anyone have an easy way to remove a suffix for all variables in a dataset?
data contamination_c; input id_c name_c :$10. age_c gender_c diagnosis1_c diagnosis2_c; datalines; 112 Albert 10 85 90 89 223 Joe 11 99 98 91 323 Jim 12 100 100 100 414 Sally 11 78 89 100 ; run; proc sql noprint; select cats(name,'=',scan(name, 1, '_')) into :suffixlist separated by ' ' from dictionary.columns where libname = 'WORK' and memname = 'contamination_c' and 'c' = scan(name, 2, '_'); quit;
That code is pretty close, you need to make sure you case matches for the memname and name to generate the rename statement.
proc sql noprint;
select cats(name,'=',scan(name, 1, '_'))
into :suffixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and upper(memname) = 'CONTAMINATION_C' and 'C' = scan(upper(name), 2, '_');
quit;
%put &suffixlist.;
data want;
set contamination_c;
rename &suffixlist.;
run;
In dictionary.columns, MEMNAME is stored in uppercase. Just change to:
... and memname = 'CONTAMINATION_C' and ...
That code is pretty close, you need to make sure you case matches for the memname and name to generate the rename statement.
proc sql noprint;
select cats(name,'=',scan(name, 1, '_'))
into :suffixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and upper(memname) = 'CONTAMINATION_C' and 'C' = scan(upper(name), 2, '_');
quit;
%put &suffixlist.;
data want;
set contamination_c;
rename &suffixlist.;
run;
It almost worked! All the variables changed except for variables with multiple underscores.
I have some variables with multiple underscores like not_enrolled_o and those variables for some reason are not dropping the "_o".
Is there something else I need to add to the code to get it to recognize those variables with multiple underscores?
Then your SQL code needs to change. Take a look at how you're determining the variables to include.
Ah, got it! Thank you so much!
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.