Rename a large number of variables

Reply
Regular Contributor
Posts: 150

Rename a large number of variables

I am working on a longitudinal study and we are collecting data every 6 months from the same participant. Our programmer decided to change the variable names for the same question from baseline to 6 months so we can't do comparisons and we have over 600 variables that are the same. We created an excel spreadsheet that has the baseline (correct variable name) and the 6 month variable name. Is there a way to read that excel spreadsheet in and have it automatically rename the variables? I am trying to avoid having to hand rename all the variables. Thanks for your help.

Super User
Posts: 17,864

Re: Rename a large number of variables

You can't have variables with the same name, so what naming convention did the programmer do and what type do you want?

Super User
Super User
Posts: 6,502

Re: Rename a large number of variables

If you have a list of the name pairs then you can use it to generate the RENAME syntax.

So if your dataset is called RENAMES and has two  variables named BASELINE and MONTH6 then you could generate the rename statement like this:

filename code temp;

data _null_;

   set renames end=eof ;

   file code;

   if _n_=1 then put 'rename';

   put baseline '=' month6 ;

   if eof then put ';' ;

run;

And then use %INCLUDE to include it in a data step.

data new;

set old;

%include code / source2;

run;

Super User
Posts: 10,516

Re: Rename a large number of variables

Tom's solution is extra nice because unless you get your "programmer" on board with the actuall project needs you'll likely end up having this happen repeatedly.

Just remember to include a variable whose value tells which collection period the data is from if you don't already have one.

Regular Contributor
Posts: 150

Re: Rename a large number of variables

Sorry I am just replying to this, I only work on this a couple days a week. I think I am a little confused by Tom's response. For the first set of code, am I running this just using the excel file to create the rename codes then applying it to the second set of code for the 6 month data set?

We do have a variable in the data called visit to distinguish the data. I was the original programmer of the baseline data and had a coworker do the programming for the 6 month data under my instruction. Unfortunately he did not listen to me when I told him about the variable names and by the time we figured out they were different we had already used the instrument so either way we would have to do these name changes. Thanks for your help.

Super User
Posts: 17,864

Re: Rename a large number of variables

It may be easier if you post what the data look likes and then what you want.

Regular Contributor
Posts: 150

Re: Rename a large number of variables

I have three files, a baseline data set in SAS form, a month6 data set in SAS form, and an excel file that has variables names from baseline, and variable names from month6 that are matched with each other. This has two columns that look like this.

baseline_vname    month6_vname

tavelmx                     tmx1

study_id                    id

The questions related to the variable names above are identical.

If the question was the same in both then I want the variable name from the 6 month data=variable name from the baseline data but the two data sets are kept separately. They need to be the same for repeated measures analysis.

Super User
Posts: 10,516

Re: Rename a large number of variables

You say you have the two sets of variables in Excel. You can create a formula to make a set of strings that look like

tmx1 = tavelmax

id = study_id

etc.

Use Proc datasets to rename all of the variables from the month6 data to the baseline using the results above in a RENAME

statement.

Ask a Question
Discussion stats
  • 7 replies
  • 644 views
  • 3 likes
  • 4 in conversation