Removing a suffix from a variable name

[einstein]

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;

[Astounding]

In dictionary.columns, MEMNAME is stored in uppercase.  Just change to:

... and memname = 'CONTAMINATION_C' and ...

[Reeza · Accepted solution]

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;

[einstein]

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?

[Reeza]

Then your SQL code needs to change. Take a look at how you're determining the variables to include.

[einstein]

Ah, got it!  Thank you so much!