Changing the var names based on a mapping table

Reply
New Contributor
Posts: 3

Changing the var names based on a mapping table

Deal All,

Suddenly, my boss has decided to change the name of some of the fields in all our SAS datasets. And this list may change on further requirement. So I have created a mapping table which has a list of new_Var name against the Old_Var list.

How can I change the variable names of the input dataset based on the mapping available in Var_Mapping to get an output like Want. I would also like to retain all the variables which are not listed in Var_Mapping datasets.

Var_Mapping

Old_Var

New_Var

ID

Emp_ID

Sr_No

Serial_Number

Cost

Base_Price

Profit

Margin

Field1

Account_Number

Field2

Journal_No

Field3

Final_Rate

Field4

Acc_Intt

Field5

Type

Input

Sr_No

Cost

Field1

Field2

Field3

Business

Units

1

3432

432d32

qwer12

1

CD

50

2

24342

123d32

qwer13

2

CD

10

3

3234

123g11

qwer14

1

CD

20

Want

Serial_Number

Base_Price

Account_Number

Journal_No

Final_Rate

Business

Units

1

3432

432d32

qwer12

1

CD

50

2

24342

123d32

qwer13

2

CD

10

3

3234

123g11

qwer14

1

CD

20

Could you please help on this!

Thanks,  TL

Super User
Super User
Posts: 7,076

Re: Changing the var names based on a mapping table

Posted in reply to techlearner986

Use the mapping list to generate the pairs needed for a RENAME statement.

proc sql noprint ;

  select catx('=',old_var,new_var)

     into :renames separated by ' '

     from var_mapping

   ;

quit;


data want ;

   set input ;

   rename &renames ;

run;


PROC Star
Posts: 7,492

Re: Changing the var names based on a mapping table

Posted in reply to techlearner986

Similar to Tom's suggested code, but you can avoid all of the warnings (for nonexistent variables) by first using proc sql to compare the two files and only rename variables that exist in both files.  e.g.:

data Var_Mapping;

  informat new_var $20.;

  input old_var $ new_var;

  cards;

ID Emp_ID

Sr_No Serial_Number

Cost Base_Price

Profit Margin

Field1 Account_Number

Field2 Journal_No

Field3 Final_Rate

Field4 Acc_Intt

Field5 Type

;

data example;

  Input (Sr_No Cost Field1 Field2 Field3 Business Units) ($);

  cards;

1 3432 432d32 qwer12 1 CD 50

2 24342 123d32 qwer13 2 CD 10

3 3234 123g11 qwer14 1 CD 20

;

proc sql noprint;

  select trim(old_var)||'='||trim(new_var)

    into :rename separated by " "

     from

      (select old_var, new_var

        from work.var_mapping

         where upcase(old_var) in

          (select upcase(name)

           from dictionary.columns

            where libname=upcase("work") and

             memname=upcase("example")))

  ;

quit;

data want;

  set example (rename=(&rename.));

run;

Super Contributor
Posts: 387

Re: Changing the var names based on a mapping table

See also the DKROCOND and DKRICOND system options.

Ask a Question
Discussion stats
  • 3 replies
  • 171 views
  • 0 likes
  • 4 in conversation