topic Re: Rename all variables starting with the same preffix to a different preffix in SAS Programming

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

Suzy_Cat — Wed, 11 Dec 2019 20:51:49 GMT

Hi there

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

Cheers

Sue

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

PaigeMiller — Wed, 11 Dec 2019 21:10:33 GMT

Rename x1-x100 to be y1-y100.

In a SAS DATA step, use:

rename x1-x100=y1-y100;

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

Suzy_Cat — Thu, 12 Dec 2019 01:19:52 GMT

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

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

PaigeMiller — Thu, 12 Dec 2019 13:03:09 GMT

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

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

gamotte — Thu, 12 Dec 2019 13:40:18 GMT

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;

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

Suzy_Cat — Sat, 14 Dec 2019 10:10:29 GMT

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

Thanks

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

Tom — Sat, 14 Dec 2019 17:53:26 GMT

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.

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

Suzy_Cat — Sun, 15 Dec 2019 20:49:18 GMT

Cheers Tom 🙂

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

ScottBass — Mon, 16 Dec 2019 02:50:21 GMT

@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

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

PaigeMiller — Mon, 16 Dec 2019 14:39:40 GMT

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;

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

Suzy_Cat — Mon, 16 Dec 2019 03:13:10 GMT

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

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

ScottBass — Mon, 16 Dec 2019 05:02:47 GMT

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

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

PaigeMiller — Mon, 16 Dec 2019 11:48:14 GMT

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