Removing a suffix from a variable name

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Removing a suffix from a variable name

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;

Accepted Solutions
Solution
‎06-20-2016 02:44 PM
Grand Advisor
Posts: 17,332

Re: Removing a suffix from a variable name

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,973

Re: Removing a suffix from a variable name

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

 

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

Solution
‎06-20-2016 02:44 PM
Grand Advisor
Posts: 17,332

Re: Removing a suffix from a variable name

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;
Contributor
Posts: 60

Re: Removing a suffix from a variable name

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?

 

Grand Advisor
Posts: 17,332

Re: Removing a suffix from a variable name

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

 

 

Contributor
Posts: 60

Re: Removing a suffix from a variable name

Ah, got it!  Thank you so much!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1310 views
  • 0 likes
  • 3 in conversation