Macro to rename variables with the same prefix

[skavli]

Hi,

I'm new to sas macro and i'm wondering how I could rename the prefix of my variables that have the same prefix using a sas macro, I have found something in the internet but it does not seem to fit, can you help me please ?

%macro RenameList(vars= , prefix=);
   %let list=;
   %do i = 1 %to %sysfunc(countw(&vars));
      %let var=%scan(&vars,&i);
      %let list= &list %sysfunc(catx(=,&var,&prefix.&var));
   %end;
   &list
%mend;

[PeterClemmensen]

Do you want to rename all variables or just some of them?

[skavli]

I have approximatively 70 variables like this IPN_RAORAOR1 .... IPN_RAORAOR41 IPN_VOLVOL1 ... IPN_VOLVOL29 and i want to substitute the prefixes with another string, I want to use a macro to be able to do that to other datasets where i might encouter the same issue

[PaigeMiller]

---

@skavli wrote:
I have approximatively 70 variables like this IPN_RAORAOR1 .... IPN_RAORAOR41 IPN_VOLVOL1 ... IPN_VOLVOL29 and i want to substitute the prefixes with another string, I want to use a macro to be able to do that to other datasets where i might encouter the same issue

---

No macro needed. A macro variable (not the same thing as a macro) is needed.

proc contents data=have out=_contents_ noprint;
run;
proc sql noprint;
    select cats(name,'=new_',substr(name,5)) into :renames separated by ' ' from have where name eqt 'IPN_';
run;
proc datasets library=work;
    modify have;
    rename &renames;
run; quit;

This renames variables in data set WORK.HAVE whose names begin with IPN_ to begin with NEW_

[Astounding]

You should realize that you are asking for complexity when simplicity will suffice.  With variables that have the same prefix and a numbered suffix, renaming is easy.  Consider:

rename IPN_RAORAOR1 - INP_RAORAOR41 = prefixA_1 - prefixA_41
       IPN_VOLVOL1 - IPNVOLVOL29    = prefixB_1 - prefixB_29;

[skavli]

I haven't expressed my problem very well, each IPN_RAORAOR does not have successive numbers in the end for example I have IPN_RAORAOR17 then IPN_RAORAOR48 sometimes IPN_RAORAOX1 etc thing is i just wanna replace the IPN_RAORAO by IPN_RAO and so on for other variables

[Tom]

---

@skavli wrote:
I haven't expressed my problem very well, each IPN_RAORAOR does not have successive numbers in the end for example I have IPN_RAORAOR17 then IPN_RAORAOR48 sometimes IPN_RAORAOX1 etc thing is i just wanna replace the IPN_RAORAO by IPN_RAO and so on for other variables

---

proc contents data=have out=contents noprint; run;
data mapping;
  set contents (keep=name);
  newname=upcase(name);
  if newname =: 'IPN_RAORAO' then
   newname= 'IPN_RAO' || substr(name,length( 'IPN_RAORAO')+1)
  ; 
run;

proc sql noprint;
  select catx('=',name,newname) into :renames separted by ' '
  from mapping where upcase(name) ne upcase(newname)
  ;
quit;

data want;
  set have;
  rename &renames;
run;

[Tom]

---

@skavli wrote:
I have approximatively 70 variables like this IPN_RAORAOR1 .... IPN_RAORAOR41 IPN_VOLVOL1 ... IPN_VOLVOL29 and i want to substitute the prefixes with another string, I want to use a macro to be able to do that to other datasets where i might encouter the same issue

---

Note that the currently selected answer does NOT do that.  It will instead add an additional prefix onto the variables, not replace the existing prefix with something else.  To do that you will need a different method.  Either a query of the variable names (form one of the metadata tables/views or PROC CONTENTS output) of if the names are simple X1-X5 type lists just a simple RENAME statement.

[skavli]

how would I proceed, can you help me with more details ?

[PaigeMiller]

---

@skavli wrote:
how would I proceed, can you help me with more details ?

---

The non-macro code I provided earlier should work.

[PaigeMiller]

---

@skavli wrote:

I have found something in the internet but it does not seem to fit

---

Why doesn't it fit? You need to explain in more detail.

Also, please provide two or three small examples of what you are trying to do.

And why does the solution have to be a macro?

[skavli]

data test;
set work.bdd_media_physique_2020_hebdo;
run;

%macro RenameList(vars= , prefix=);
   %let list=;
   %do i = 1 %to %sysfunc(countw(&vars));
      %let var=%scan(&vars,&i);
      %let list= &list %sysfunc(catx(=,&var,&prefix.&var));
   %end;
   &list
%mend;

data test2;
set test;
run;

%RenameList(vars = IPN_AFFAFF: IPN_RAORAO: IPN_VOLVOL:, prefix = IPN_AFFR IPN_RADON IPN_VOL);

Knowing that my variables have prefixes like what I mentioned in the code (IPN_AFFAFF IPN_RAORAO IPN_VOLVOL ) however the ending is definitely not the same and the variables does not end with the successive numbers, for the macro question i want to use a macro to be able to use elswhere on other datasets where i might encounter the same issue

[Tom]

You are not using the macro the way it was intended.  First it just generates part of a statement so you need to call it in a RENAME statement or RENAME= option.  Second the macro does not support variable lists, only lists of actual variable names. And third the macro does not support multiple different prefixes.

First let's fix the logic of the macro.  There is no reason to use CATX() for macro variable concatenation. Just expand the macro variables next to each other.  Also it does not make its local macro variables local.  So the macro could be re-written as this:

%macro RenameList(vars= , prefix=);
   %local i var;
   %do i = 1 %to %sysfunc(countw(&vars,%str( )));
     %let var=%scan(&vars,&i,%str( ));
     &var=&prefix.&var
   %end;
%mend;

Now how can we make a way that you can use a variable list like IPN_AFFAFF: IPN_RAORAO: IPN_VOLVOL: ?

Here is a way using PROC TRANSPOSE and PROC SQL INTO to convert VARLIST into NAMELIST.

%let varlist=IPN_AFFAFF: IPN_RAORAO: IPN_VOLVOL: ;
proc transpose data=have (obs=0 keep=&varlist)  out=names ; var _all_; run;
proc sql noprint;
select _name_ into :namelist separated by ' ' 
from names
;
quit;

Now can we make it use multiple prefixes?  It would be more complex to try to do it the style of the first macro that only generates part of a statement.  (somethings called a "function" macro).  So let's not bother and either just write a program or write a macro that generate multiple SAS steps.  SO just use a loop to take each pair from VARLIST and PREFIXLIST and build up one long rename list.  Then run a step that will use the list to do the renaming.  

%macro rename(inds,outds,varlist,prefixlist);
%local i vars prefix namelist renames;
%do i=1 %to %sysfunc(countw(&varlist,%str( )));
  %let vars=%scan(&varlist,&i,%str( ));
  %let prefix=%scan(&prefixlist,&i,%str( ));
proc transpose data=&inds(obs=0 keep=&vars) out=names; var _all_; run;
proc sql noprint;
  select _name_ into : namelist separated by ' ' from names;
quit;
  %let renames=&renames %renamelist(vars=&namelist,prefix=&prefix);
%end;

data &outds;
  set &inds(rename=(&renames));
run;
%mend ; 

Now your call becomes:

%renamet(inds=test,outds=test2
,varlist = IPN_AFFAFF: IPN_RAORAO: IPN_VOLVOL:
,prefixlist = IPN_AFFR IPN_RADON IPN_VOL
);