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.
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.