topic Re: How to remove suffix from variables in a column? in SAS Programming

How to remove suffix from variables in a column?

orchid_sugar — Tue, 24 Jan 2023 00:20:32 GMT

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

Re: How to remove suffix from variables in a column?

Ksharp — Tue, 24 Jan 2023 05:12:51 GMT

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;

Re: How to remove suffix from variables in a column?

Tom — Tue, 24 Jan 2023 05:18:21 GMT

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;

Re: How to remove suffix from variables in a column?

PaigeMiller — Tue, 24 Jan 2023 13:54:47 GMT

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

Re: How to remove suffix from variables in a column?

orchid_sugar — Tue, 24 Jan 2023 20:06:42 GMT

Yes, I meant as values. I apologize for using the wrong term.

Re: How to remove suffix from variables in a column?

PaigeMiller — Tue, 24 Jan 2023 20:21:11 GMT

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.