Rename variable names using macro or array

[Alucard_L]

Hello everyone. I have two datasets A and B.  They have some same variables but with different names. I'd like to rename these variable names in A using the variable names in B. For example, I have variable names StaffID, Cartype, Food, Alcohol, ... up to 200 variables in dataset A.  ID, car, food, alcohollevel,....  up to 300 variables in dataset B. I want to rename staffID=ID, cartype=car, alcohol= alcohollevel. Is there a way to rename all the 200 variables in dataset A using array or macros?

Thanks,

[Reeza]

Are the data sets ordered in the exact same manner - variables are listed in the exact same order?

How do you know which names you want from which data set?

[Alucard_L]

They are not in the same order. Variable names in dataset B are the names I want. I need to change variable names in dataset A. I have the mapping chart. So I know which variable maps to which.

[ballardw]

---

@Alucard_L wrote:
They are not in the same order. Variable names in dataset B are the names I want. I need to change variable names in dataset A. I have the mapping chart. So I know which variable maps to which.

---

Show your mapping chart.

If you can create a data step with the old/new pairs then it is very likely we can use that with Proc Datasets to change as requested.

[Alucard_L]

mapping chart
A               B
StaffID      ID
Cartype    car
Food        food
Alcohol     alcohollevel

[Reeza]

https://communities.sas.com/t5/Base-SAS-Programming/How-to-write-a-macro-for-renaming-lots-of-variab...

[Astounding]

GIven that you haven't done this before, here is a simple way.  Create your own RENAME statement, stored in a file.  For example:

filename myfile 'path to a file of your choice';

data _null_;

file myfile noprint;

put 'rename';

do until (done);

   set mapping_chart end=done;

   put a '=' b;

end;

put ';' ;

stop;

run;

Inspect the file, so you understand how the program works.  Then apply it in whatever way you are comfortable.  A DATA step has the disadvantage of needing to read the data, but the advantage of being simple:

data want;

set have;

%include myfile;

run;

Or you can use PROC DATASETS with the MODIFY statement.  Either way should get the job done.

[Alucard_L]

Thanks! Your method also works!

[art297 · Accepted solution]

The following should work:

data a;
  input StaffID Cartype Food Alcohol;
  cards;
1 2 3 4
5 6 7 8
;

data B;
  input ID car food alcohollevel;
  cards;
1 2 3 4
5 6 7 8
;

data mapping_chart;
  informat A B $32.;
  input A B;
  cards;
StaffID      ID
Cartype    car
Food        food
Alcohol     alcohollevel
;

proc sql;
  select catt(A,'=',B)
    into :renames separated by ' '
      from mapping_chart
  ;
quit;

proc datasets lib=work nolist;
  modify A;
    rename &renames.;
quit;
run;

Art, CEO, AnalystFinder.com