05-23-2013 06:50 PM
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.
05-23-2013 08:26 PM
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;
set renames end=eof ;
if _n_=1 then put 'rename';
put baseline '=' month6 ;
if eof then put ';' ;
And then use %INCLUDE to include it in a data step.
%include code / source2;
05-24-2013 10:51 AM
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.
05-30-2013 01:53 PM
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.
05-30-2013 02:22 PM
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.
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.
05-30-2013 04:32 PM
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
Use Proc datasets to rename all of the variables from the month6 data to the baseline using the results above in a RENAME