topic Re: Macro to rename variables with the same prefix in SAS Studio

Macro to rename variables with the same prefix

skavli — Fri, 31 Jul 2020 10:43:29 GMT

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;

Re: Macro to rename variables with the same prefix

PeterClemmensen — Fri, 31 Jul 2020 10:52:01 GMT

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

Re: Macro to rename variables with the same prefix

PaigeMiller — Fri, 31 Jul 2020 11:27:01 GMT

@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?

Re: Macro to rename variables with the same prefix

skavli — Fri, 31 Jul 2020 11:50:47 GMT

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

Re: Macro to rename variables with the same prefix

skavli — Fri, 31 Jul 2020 11:55:41 GMT

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

Re: Macro to rename variables with the same prefix

PaigeMiller — Fri, 31 Jul 2020 12:14:56 GMT

@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_

Re: Macro to rename variables with the same prefix

Tom — Fri, 31 Jul 2020 15:18:36 GMT

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
);

Re: Macro to rename variables with the same prefix

Astounding — Fri, 31 Jul 2020 14:50:53 GMT

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;

Re: Macro to rename variables with the same prefix

Tom — Fri, 31 Jul 2020 14:57:20 GMT

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

Re: Macro to rename variables with the same prefix

skavli — Fri, 31 Jul 2020 14:59:40 GMT

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

Re: Macro to rename variables with the same prefix

skavli — Fri, 31 Jul 2020 15:10:27 GMT

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

Re: Macro to rename variables with the same prefix

PaigeMiller — Fri, 31 Jul 2020 15:17:43 GMT

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

The non-macro code I provided earlier should work.

Re: Macro to rename variables with the same prefix

Tom — Fri, 31 Jul 2020 15:24:56 GMT

@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;