topic Re: Removing a suffix from a variable name in SAS Programming

Removing a suffix from a variable name

einstein — Mon, 20 Jun 2016 17:20:30 GMT

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;

Re: Removing a suffix from a variable name

Astounding — Mon, 20 Jun 2016 17:32:05 GMT

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

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

Re: Removing a suffix from a variable name

Reeza — Mon, 20 Jun 2016 17:32:54 GMT

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;

Re: Removing a suffix from a variable name

einstein — Mon, 20 Jun 2016 17:47:09 GMT

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?

Re: Removing a suffix from a variable name

Reeza — Mon, 20 Jun 2016 18:03:34 GMT

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

Re: Removing a suffix from a variable name

einstein — Mon, 20 Jun 2016 18:44:08 GMT

Ah, got it! Thank you so much!