Rename Variables

[rfarmenta]

I posted about this previously but think I got confused and in turn confused others so I am going to re-try here.

I am working on a longitudinal study collecting data at 5 points in time. We completed the first visit and created a new questionnaire for the second visit. However, our programmer made a mistake and if the question was the same, he created a new variable name instead of keeping it the same. What we wanted was the variable names to remain the same and for them to be identified by the variable "visit_number" to look at change over time. So I have an excel file that has all the variable names from visit 1, matched up with the variables names from visit 2 if the question was identical. What I need to do is convert the variable names from visit 2 to equal the variable name of visit 1 if the question was the same.

Visit 1 varname               Visit 2 varname

travel6mo                         trav6mo

varaa                               varrra

usedrg                             drguse

So I need the visit 2 varnames to be replaces by those in visit 1. I know I can use the RENAME statement but I have about 900 variables that need to be renamed, that is why we created an excel file first, to match things up. Please let me know if there is a way to do this. Please let me know if you have any questions.

[esjackso]

Do you have two datasets (first visit and second visit)? Could you force append the 2nd dataset to a shell of the first dataset? The variable positions would have to be identical in both datasets for this to work.

Then you wouldnt have to code the renaming at all.  Not seeing the full structure of the data its hard to tell if this will help solve your problem or not.

EJ

[rfarmenta]

The two data sets are not identical as several questions were removed from the questionnaire for follow up and a few new ones were added.

[Tom]

Use the list to generate code you can use in a RENAME statement.

proc sql noprint ;

select catx('=',Visit_2_varname,Visit_1_varname) into :renames separated by ' '

from mytable

;

quit;

data want ;

  set visit2 ;

  rename &renames;

run;

[Haikuo · Accepted solution]

Same idea as Tom, but if all you need is to rename, you don't have to create a new table, and if your table is huge, it will save you some time considerably.

proc sql;

  select cats(visit2,'=',visit1) into :rename separated by ' ' from rename;

quit;

proc datasets library=yourlibraryname nolist;

  modify visit2;

  rename &rename;

quit;

Haikuo

[rfarmenta]

Thank you, I think Hai.Kuo's solution worked. I am going through to make sure everything matched up correctly. I will add an update tomorrow once I know everything worked correctly. I really appreciate everyone's expertise and willingness to help a novice trying to improve his skills.