Rename all variables starting with the same preffix to a different preffix

[Suzy_Cat]

Hi there

Can anyone help me on renaming all variables starting with the same preffix to a different prefix?

Cheers

Sue

[PaigeMiller]

Rename x1-x100 to be y1-y100.

In a SAS DATA step, use:

rename x1-x100=y1-y100;

[Suzy_Cat]

Hi Paige,

Thanks for the suggestions. It is working when I tried below:

rename Cat_:=Sohi_:;

the name of those datasets is not ending with digits and they are random combination 

[PaigeMiller]

---

@Suzy_Cat wrote:

Hi Paige,

Thanks for the suggestions. It is working when I tried below:

rename Cat_:=Sohi_:;

---

Did you mean to say that it is NOT working??

[Suzy_Cat]

Hi Paige , yes you were right. did not realize there was a type while I actually meant it not working as expected.

Thanks

[Tom]

It is impossible for that syntax to work given how the : suffix works in variable lists.  When you use the : suffix you are asking SAS to list all variable in the current data step that start with that prefix.  So when you use it on the right of the equal sign in a RENAME statement it will fail in two ways.  Either it won't find the variables since they haven't been defined yet. Or it will find the variables, in which case they cannot be used as the new names since they already exist.

[Suzy_Cat]

Cheers Tom 🙂

[gamotte · Accepted solution]

Hello,

data have; 
    input a_a a_b a_1 a_2 a_1000;
    cards;
1 2 3 4 5
;
run;

data _NULL_;
    call execute('data want; set have; rename');

    do until(fend);
        set sashelp.vcolumn end=fend;
        where libname="WORK" and memname="HAVE" and NAME like 'a_%';
        call execute(cats(NAME,'=b_', substr(NAME,3)));
    end;

call execute('; run;');
stop;
run;

[ScottBass]

---

@Suzy_Cat wrote:

Hi there

Can anyone help me on renaming all variables starting with the same preffix to a different prefix?

Cheers

Sue

---

@gamotte 's solution works fine, but would perform poorly if the target dataset was large.

Here is another approach:

data have;
   length foo a_a a_b x_y x_z a_c bar blah 8;
run;

proc sql noprint;
   select name into :vars separated by " "
   from dictionary.columns
   where libname="WORK" 
     and memname="HAVE"
     and name like "a_%"
   ;
quit;

%put &=vars;

%macro code;
&word=newprefix_%scan(&word,2,_)
%mend;

proc datasets lib=work nolist;
   modify have;
   rename %loop(&vars);
quit;

https://github.com/scottbass/SAS/blob/master/Macro/loop.sas

[PaigeMiller]

I'm glad you said that @ScottBass. Rename using PROC DATASETS is preferable to rename in a DATA step.

Even more compact code, with no macro looping

proc sql noprint;
   select cats(name,'=',tranwrd(name,'a_','b_')) into :renames separated by " "
   from dictionary.columns
   where libname="WORK" 
     and memname="HAVE"
     and name eqt "a_"
   ;
quit;
proc datasets library=work nolist;
    modify have;
    rename &renames;
quit;
  

[ScottBass]

@PaigeMiller Yep your code is more compact.  I find my %loop macro so useful (to me anyway) that I may have a bit of tunnel vision.

[PaigeMiller]

@ScottBass you don't even show your %loop macro, so your code above won't work for anyone but you.

[Suzy_Cat]

You guys are awesome! provided more effective ways. good learning! Thanks a lot 🙂