Rename variables within loop - ambiguous abbreviation error

[Lucia Pacca]

Hello,
I have biannual files called "job_history" with a year suffix at the end (job_history_1992, job_history_1994, etc.). Within each of these files, I have 65 "state" variables with suffix at the end, from state1 to state65. I want to add, within each file, the year suffix to the name of the "state" variables. So, for 1992, I would have state1_1992, state2_1992, etc.
I am trying to do it with the following loop:

foreach data in job_history_1992 job_history_1994 job_history_1996 job_history_1998 job_history_2000 job_history_2002 job_history_2004 job_history_2006 job_history_2008 job_history_2010 job_history_2012 job_history_2014 job_history_2016 job_history_2018 {
local i = substr("`data'", 13, .)
foreach var of varlist state1 state2 state3 state4 state5 state6 state7 state8 state9 state10 state11 state12 state13 state14 state15 state16 state17 state18 state19 state20 state21 state22 state23 state24 state25 state26 state27 state28 state29 state30 state31 state32 state33 state34 state35 state36 state37 state38 state39 state40 state41 state42 state43 state44 state45 state46 state47 state48 state49 state50 state51 state52 state53 state54 state55 state56 state57 state58 state59 state60 state61 state62 state63 state64 state65{
rename `var' `var'_`i'
save, replace
}
}

And I am getting the following error: state1 ambiguous abbreviation (see below)

Is it because variables have identical names across files? How would I solve this issue?

Thank you so much!

-Lucia

[Øyvind Snilsberg]

forval i = 1992(2)2018 {
use job_history_`i', clear
rename state* state*_`i'
save job_history_`i', replace
}

you might want to consider reshaping your data

[Clyde Schechter]

Well, the ambiguity that Stata is encountering is that when it sees -rename state1 state1_`i'- it does not know whether state1 refers to state1 itself, or state10, or state11,...,state19. The solution is really to eliminate the inner loop and rely on group renaming:

foreach data in job_history_1992 job_history_1994 job_history_1996 job_history_1998 job_history_2000 job_history_2002 job_history_2004 job_history_2006 job_history_2008 job_history_2010 job_history_2012 job_history_2014 job_history_2016 job_history_2018 {
    local i = substr("`data'", 13, .)
    rename (state*) =_`i'
    save, replace
}

That said, though, your code looks seriously flawed in another respect. You are looping over these filenames, but you never actually bring the data from those files into memory. So I think what you want is:

foreach data in job_history_1992 job_history_1994 job_history_1996 job_history_1998 job_history_2000 job_history_2002 job_history_2004 job_history_2006 job_history_2008 job_history_2010 job_history_2012 job_history_2014 job_history_2016 job_history_2018 {
    use `data', clear
    local i = substr("`data'", 13, .)
    rename (state*) =_`i'
    save, replace
}

Added: crossed with #2.

Finally, although this code looks correct to me, before you run it you should either back up the original files, or you should replace -save, replace- by a command to save them under different names. You don't want to clobber the data sets and then find you've both messed it up and lost the original data.

[Anthony Macedo]

Hello, I got a similar error using “forvalues”, so I would really appreciate if you can try to help me.
I have variables from x_a_1 to x_a_2856, from x_b_1 to x_b_2856, and from x_c_1 to x_c_2856. Now, I would like to rename them all from x_a_1 to x_a_8568.
Therefore, I tried the following code:

forval num = 1/2856 {
rename impmth2_`num' impmth_`=`num'+2856'
}
forval num = 1(1)2856 {
rename impmth3_`num' impmth_`=`num'+5712'
}

This actually worked for the variables between 5713 and 8568, but I got the error for the first 3 code lines.
Thank you for your attention.

[Clyde Schechter]

I don't understand how the code could have worked at all. You have variables with names like x_a_* and x_b_*, etc. and the code refers to variables named impmth2_* and impmth3_*. Something doesn't add up here.

Anyway, let me ignore the code and just respond to your description. Just to keep the number of variables more manageable for example, I create a toy data set where the numbers of the x_a_*, x_b_*, and x_c_* just run up to 200:

clear*
foreach l in a b c {
    forvalues i = 1/200 {
        gen x_`l'_`i' = .
    }
}

So the above creates a data set that, if I understand your situation, is a miniature version of yours, though it also has only missing values for the data (the actual data are not relevant to this task).

You can accomplish the renaming to all x_a_* variables as follows:

rename x_b_# x_a_#, renumber(201)
rename x_c_# x_a_#, renumber(401)

You need only change 201 and 401 to the corresponding multiples of 2856 plus 1 for this to work in your real data set.

[Anthony Macedo]

Indeed, I tried to use simpler variable names to explain my problem, but then I forgot to adjust the code. I am sorry for that.
Anyway, your solution worked perfectly. Thank you very much.