topic Rename many variables with similar names in SAS Programming

Rename many variables with similar names

gracegon103 — Fri, 16 Nov 2018 19:53:03 GMT

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

Re: Rename many variables with similar names

JoshB — Fri, 16 Nov 2018 20:13:10 GMT

@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-p/148914/highlight/true#M29415

Re: Rename many variables with similar names

Shmuel — Fri, 16 Nov 2018 20:43:11 GMT

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 */

Re: Rename many variables with similar names

Reeza — Fri, 16 Nov 2018 20:40:57 GMT

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.

Re: Rename many variables with similar names

ballardw — Fri, 16 Nov 2018 22:35:12 GMT

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

Re: Rename many variables with similar names

gracegon103 — Mon, 19 Nov 2018 17:08:44 GMT

Thanks!!! It really helped!