Rename variable names using macro or array

Accepted Solution Solved
Reply
Highlighted
New Contributor
Posts: 4
Accepted Solution

Rename variable names using macro or array

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,


Accepted Solutions
Solution
2 weeks ago
PROC Star
Posts: 7,800

Re: Rename variable names using macro or array

Posted in reply to Alucard_L

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

 

View solution in original post


All Replies
Super User
Posts: 21,546

Re: Rename variable names using macro or array

Posted in reply to Alucard_L

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?

New Contributor
Posts: 4

Re: Rename variable names using macro or array

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.
Super User
Posts: 12,148

Re: Rename variable names using macro or array

Posted in reply to Alucard_L

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.

New Contributor
Posts: 4

Re: Rename variable names using macro or array

[ Edited ]

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

Super User
Posts: 21,546
Super User
Posts: 6,004

Re: Rename variable names using macro or array

Posted in reply to Alucard_L

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.

New Contributor
Posts: 4

Re: Rename variable names using macro or array

Posted in reply to Astounding
Thanks! Your method also works!
Solution
2 weeks ago
PROC Star
Posts: 7,800

Re: Rename variable names using macro or array

Posted in reply to Alucard_L

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 191 views
  • 0 likes
  • 5 in conversation