I have a column where all the variables have suffixes (for example, variable name: procCode. variable options: x_ratio, y_ratio, z_ratio, a_ratio...). I want the variables to be x, y, z, a and remove the suffix '_ratio'. Is there a method where I don't have to do rename = (x_ratio=x y_ratio=y .....)?
If the value always ends in
_ratio
then you can remove _ratio with the following code
proccode=substr(proccode,1,length(proccode)-6);
If sometimes it ends with _ratio and sometimes it doesn't end with _ratio, or sometime _ratio is in the middle of the value (such as x_ratio_previous), then you need more programming.
data have;
input x_ratio y_ratio z_ratio a_ratio;
cards;
1 2 3 4
;
proc transpose data=have(obs=0) out=temp;
var _all_;
run;
proc sql noprint;
select catx('=',_NAME_,scan(_NAME_,1,'_')) into :rename separated by ' ' from temp;
quit;
proc datasets library=work nolist nodetails;
modify have;
rename &rename.;
quit;
Assuming you mean that you have a DATASET that has VARIABLES named X_RATIO, Y_RATIO etc.
Then the answer is No. If you want to rename variable you need to use a rename statement or a rename= dataset option. But that does not mean you have to TYPE them out yourself.
Get the list of variables that match your pattern and generate the OLD=NEW pairs into a macro variable. Then use the macro variable to generate the RENAME.
So if the data is named HAVE in the WORK library then you might use code like:
proc sql noprint;
select catx('=',nliteral(name),nliteral(substr(name,1,length(name)-6))
into :renames separated by ' '
from dictionary.columns
where libname='WORK' and memname='HAVE'
and upcase(name) like '%^_RATIO' escape '^'
;
quit;
proc datasets lib=work nolist;
modify have;
rename &renames;
run;
quit;
@orchid_sugar wrote:
I have a column where all the variables have suffixes (for example, variable name: procCode. variable options: x_ratio, y_ratio, z_ratio, a_ratio...). I want the variables to be x, y, z, a and remove the suffix '_ratio'. Is there a method where I don't have to do rename = (x_ratio=x y_ratio=y .....)?
Well I have to say I read the problem differently than the others. So, @orchid_sugar do you mean
I have a column where all the values have suffixes (for example, variable name: procCode. variable values: x_ratio, y_ratio, z_ratio, a_ratio...). I want the values to be x, y, z, a and remove the suffix '_ratio'.
Or do you mean
I have variables where all the variable names have suffixes (for example, variable name: procCode. variable options: x_ratio, y_ratio, z_ratio, a_ratio...). I want the variable names to be x, y, z, a and remove the suffix '_ratio'.
Yes, I meant as values. I apologize for using the wrong term.
If the value always ends in
_ratio
then you can remove _ratio with the following code
proccode=substr(proccode,1,length(proccode)-6);
If sometimes it ends with _ratio and sometimes it doesn't end with _ratio, or sometime _ratio is in the middle of the value (such as x_ratio_previous), then you need more programming.
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.