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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.