Rename many variables with similar names

[gracegon103]

I have many variables  as the following (Year and Month go from 20170701 until 20170930: 

FACE_YEARMONTHDAY

Bucket_YEARMONTHDAY

I have saved a list of this variables in variables1709, variables1708  and variables1707 as follows:

proc contents data= charge.actdays_&yymm. noprint out=conts&yymm; run;

proc sql noprint;
select name into :variables&yymm. separated by " " from conts&yymm where
(name like "FACE%") or
(name like "BUCKET%") ;

quit;

But i need to rename them so that every variable that begins with "FACE" now begins with "AMT" and every variable that begins with "BUCKET" now begins with "SERVICE".

[JoshB]

@Ksharp has a great solution here I often use. You'll need to cater it a
bit to your need but should work just fine for you.

https://communities.sas.com/t5/SAS-Programming/Renaming-a-group-of-variables-with-the-same-prefix/m-...

[Shmuel · Accepted solution]

For a small number of variables you can start with:

proc contents data= charge.actdays_&yymm. noprint out=conts&yymm; run;

data _null_;
  set  conts&yymm;
        new_name = tranwrd(name,'FACE_','AMT_');
        new_name = tranwrd(name,'BUCKET_','SERVICE_');
        ....

But as you need to rename many variables better create a format:

proc format lib=work;
    value $ren
       'FACE' = 'AMT'
       'BUCKET' = 'SERVICE'
      ....
   ;
run;

data temp;
 set conts&yymm;
      length v1 v2 $40;
      v1 = strip(scan(name,1,'_');
      v2 = strip(putc(v1,$ren.));
      new_name = catx('_',v2,substr(name,length(v2)+1));

    /* next code common to both methods */
     keep name new_name;
run;

data _null_;
  set temp end = eof;
        length ren_list $1000; /* adapt length to max needed */
        retain ren_list;
        ren_list = strip(ren_list) || strip(name) || '=' ||strip(new_name);

      if eof then call symput('REN_LIST", strip(ren_list));
run;

data want;
  set have(rename = (&ren_list));
run;

/* last step can be done with proc datasets in case no need to copy the data */

[gracegon103]

Thanks!!! It really helped!

[Reeza]

https://gist.github.com/statgeek/82d9f2854edc01560e0f

You can use the same approach as above, just change what you're putting into the macro variable. Have it generate the text, Bucket0107 to AMT01017 by using some string functions. An example is in the link.

[ballardw]

---

@gracegon103 wrote:

I have many variables  as the following (Year and Month go from 20170701 until 20170930: 

FACE_YEARMONTHDAY

Bucket_YEARMONTHDAY

I have saved a list of this variables in variables1709, variables1708  and variables1707 as follows:

proc contents data= charge.actdays_&yymm. noprint out=conts&yymm; run;

proc sql noprint;
select name into :variables&yymm. separated by " " from conts&yymm where
(name like "FACE%") or
(name like "BUCKET%") ;

quit;

But i need to rename them so that every variable that begins with "FACE" now begins with "AMT" and every variable that begins with "BUCKET" now begins with "SERVICE".

---

Almost any data set that has variables with dates as part of the name, especially if you are continuing to add them , likely would benefit from transposition to a long data set with an actual date of the value and then the value

Such as

DATE   Face  Bucket

Then it becomes extremely easy to 1) add data as no "new variable names" are needed, 2) perform data queries based on dates 3) group data for summaries by use of date formats.

And as a last benefit you would only be renaming 2(in this case) variables instead of nearly 200.