Mistake in loop for renaming variables of several datafiles

[Joana Bennet]

Dear Statalists,
I have ten identically structured excel files which correspond to ten different years. All files include 18 variables which indentify an agegroup. For example the first agegroup variable is called "Underfiveyears", the second "Fivetonineyears" and the last "Overeightyyears". I want to use a loop which imports the excel files and renames these agegroup variables into age1, age2,...age18. Therefore, I used this loop:

forvalues i=1990/1999 {
import excel file`i', firstrow clear
foreach var of varlist Underfiveyears-Overeightyyears {
local x= `x'+1
rename `var' age`x'
save file_`i',replace
}
}

The problem is, that the command works for the first file but the numbering of the variables just continues for the following files instead of beginning with age1,...,. For example, the last agegroup variable in the first file is renamed age18 but the first agegroup variable in the second file is renamed age19 instead of age1.
Can someone help me to find a solution?
Thank you in advance!

[Wouter Wakker]

Instead of looping over variables you could use the many options that rename group has, see help rename group. So something like:

forvalues i=1990/1999 {
    import excel file`i', firstrow clear
    ds
    rename (`r(varlist)') age#, addnumber
    save file_`i',replace
    }

[Nick Cox]

Loops keep on looping unless you arrange otherwise. This was your intent:

forvalues i=1990/1999 {
    import excel file`i', firstrow clear
    local x = 0
    foreach var of varlist Underfiveyears-Overeightyyears {
       local x= `x'+1
       rename `var' age`x'
       save file_`i',replace
    }
}

but the loop isn't needed either.

forvalues i=1990/1999 {
    import excel file`i', firstrow clear
    rename (Underfiveyears-Overeightyyears) age#, addnumber
    save file_`i',replace
}

Note that unless each file contains data 1990 ... 1999 -- as the case fits -- you'll need to arrange that to be included in the dataset.

[Joana Bennet]

Thank you Wouter and Nick for your quick responses, using rename and the addnumber option worked perfectly well!