SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Rename variables based on Mapping Table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Rename variables based on Mapping Table

Dear all,

 

I am importing several datasets into SAS which need to be harmonised following specific naming conventions. As such I need to rename the variables of diferente datasets from A to A', B to B', C to C', etc...

As the rename function implies that I have to edit every single import, I prefer to use a centralised dataset which contains the mapping between A and A', B and B', C and C', etc...

 

My problem is how can I link the imported table to the mapping tablel, producing the final renamed field table.

 

Thank you very much for your help.

 

Kind regards,

Fernando

 

 


Accepted Solutions
Solution
‎03-08-2016 07:19 AM
Contributor
Posts: 37

Re: Rename variables based on Mapping Table

Hello Escada,

 

You will find perfect solutions to your issue here:

 

https://communities.sas.com/t5/SAS-Data-Management/How-to-dynamically-rename-multiple-variables/m-p/...

 

I had also faced same issues in past.

 

Hope it helps.

 

Thanks,

Harshad M.

View solution in original post


All Replies
PROC Star
Posts: 1,090

Re: Rename variables based on Mapping Table

Assume you have Table1, with field A -> APrime, B -> BPrime ... D -> Dprime,

and Table2 with E -> EPrime and F -> Fprime

 

wouldn't a simple lookup table like

 

Table From To
Table1 A APrime
Table1 B BPrime
Table1 C CPrime
Table1 D DPrime
Table2 E EPrime
Table2 F FPrime

 

work?

 

Tom

Occasional Contributor
Posts: 17

Re: Rename variables based on Mapping Table

Hi Tom, thank you very much for your reply. Meanwhile I found the correct answer for my question.

KR,
Fernando
Super User
Posts: 10,476

Re: Rename variables based on Mapping Table

Here is a simple use of data set containing name from - to pairs, variable name and rename in a dataset to rename those variables in an exisitng dataset. Proc Datasets is used to modify existing data sets in place to rename variables or modify labels and formats.

 

 data _null_;
   set work.rename end=LastName;
   /* this is the header part of proc datasets that is similar for each rename operation
      change the name of the dataset in the modify statement*/
   if _n_ = 1 then do;
      Call execute ("Proc datasets library=MyLib nodetails nolist;");
      Call execute ("modify MyDataset;");
      /* starts rename*/
      Call execute ("rename ")  ;
   end;
   /* writes a list of the from - to name pairs using the data in work.rename*/
   Call execute(catx(' ',name,' = ',newname)) ;
   /* after the last pair end the rename clause*/
   if LastName then do;
      Call execute (";") ;
      Call execute ("quit;");
   end;
run;

You could have a variable in the working set with the dataset name and modify the code to use first. and last. notation based on the dataset .

 

Occasional Contributor
Posts: 17

Re: Rename variables based on Mapping Table

Thank you very much Ballardw.
In fact I found a more easy way of renaming the variables.

KR,
Fernando
Solution
‎03-08-2016 07:19 AM
Contributor
Posts: 37

Re: Rename variables based on Mapping Table

Hello Escada,

 

You will find perfect solutions to your issue here:

 

https://communities.sas.com/t5/SAS-Data-Management/How-to-dynamically-rename-multiple-variables/m-p/...

 

I had also faced same issues in past.

 

Hope it helps.

 

Thanks,

Harshad M.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 530 views
  • 4 likes
  • 4 in conversation