topic Re: Rename variables based on Mapping Table in SAS Data Management

Rename variables based on Mapping Table

Escada — Mon, 07 Mar 2016 18:54:55 GMT

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

Re: Rename variables based on Mapping Table

TomKari — Mon, 07 Mar 2016 19:14:09 GMT

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

Re: Rename variables based on Mapping Table

ballardw — Mon, 07 Mar 2016 21:29:55 GMT

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 .

Re: Rename variables based on Mapping Table

HarshadMadhamshettiwar — Tue, 08 Mar 2016 11:22:16 GMT

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/255203#M6939

I had also faced same issues in past.

Hope it helps.

Thanks,

Harshad M.

Re: Rename variables based on Mapping Table

Escada — Tue, 08 Mar 2016 12:20:12 GMT

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

KR,
Fernando

Re: Rename variables based on Mapping Table

Escada — Tue, 08 Mar 2016 12:25:49 GMT

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

KR,
Fernando