topic Re: Rename variable names using macro or array in SAS Programming

Rename variable names using macro or array

Alucard_L — Fri, 09 Feb 2018 17:54:50 GMT

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,

Re: Rename variable names using macro or array

Reeza — Fri, 09 Feb 2018 18:21:31 GMT

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?

Re: Rename variable names using macro or array

Alucard_L — Fri, 09 Feb 2018 18:26:08 GMT

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.

Re: Rename variable names using macro or array

ballardw — Fri, 09 Feb 2018 18:28:48 GMT

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

Re: Rename variable names using macro or array

Alucard_L — Fri, 09 Feb 2018 18:33:17 GMT

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

Re: Rename variable names using macro or array

Reeza — Fri, 09 Feb 2018 18:37:24 GMT

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

Re: Rename variable names using macro or array

Astounding — Fri, 09 Feb 2018 18:48:02 GMT

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.

Re: Rename variable names using macro or array

art297 — Fri, 09 Feb 2018 18:48:13 GMT

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

Re: Rename variable names using macro or array

Alucard_L — Fri, 09 Feb 2018 19:50:05 GMT

Thanks! Your method also works!