error: rename using local inside loop

[Sebastian Kruk]

Dear Stata-users,

I have a problem with rename using local inside loop.

I would like to import an excel file with some block with same structure in the same page.

I use a loop but appear an error when variables are renamed.

I run:

    forvalues i = 5(30)275 {
        local b =  `i'+27
        local combustibles "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes"
        import excel "Series de oferta y demanda (1).xlsx", cellrange(A`i':AZ`b') firstrow clear
        rename A Country
        foreach v of varlist B-AZ {
            local x : variable label `v'
            rename `v' `combustibles'`x'
            }
        reshape long `combustibles', i(Country) j(year)
        save `combustibles', replace
        }

Then appear an error:

syntax error
    Syntax is
        rename  oldname    newname   [, renumber[(#)] addnumber[(#)] sort ...]
        rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
        rename  oldnames              , {upper|lower|proper}
r(198);

It works if I used:

    forvalues i = 5(30)275 {
        local b =  `i'+27
        local combustibles "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes"
        import excel "Series de oferta y demanda (1).xlsx", cellrange(A`i':AZ`b') firstrow clear
        rename A Country
        foreach v of varlist B-AZ {
            local x : variable label `v'
            rename `v' c_`i'_`b'`x'
            }
        reshape long c_`i'_`b', i(Country) j(year)
        save `combustibles', replace
        }

What can I do?

Thanks in advance,

Sebastián.

[Clyde Schechter]

The local macro combustibles contains the entire string "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes". So the command

rename `v' `combustibles'`x'

gets interpreted by Stata (I'm giving the example of the very first iteration where `v' == "B"

rename  B Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes whatever `x' happens to be

So what follows B isn't even remotely a possible variable name. It's got embedded blanks and it is way, way too long. I'm also pretty sure it isn't remotely what you want.

The -rename- command in your second block of code does not suffer any of these problems: it offers a perfectly legitimate variable name, and it contains the contents of `x', which presumably identifies what column B (or later, C-AZ) represents. This will continue to work providing all of those "labels" are sufficiently short and don't contain anything other than letters, digits, and underscore (_) characters. Since you seem not to be happy with that, my question to you is what exactly do you really want these new variable names to be?

That said, your larger code structure, however, does not make sense. Every time you iterate the outer (-forvalues i = ...-) loop, the data are saved in a file named "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes.dta". Since you are overwriting the same file each time, you are losing the information from all but the final iteration of the loop. So you might just as well forget the -forvalues i- loop and just do the code with local i = 275. I'm fairly confident this isn't actually what you want.

Here's my guess what you want: I think it is not a coincidence that the number of different values of i when you loop -forvalues i = 5(30)275- is exactly the same as the number of words contained in local macro combustibles. So I imagine you want the first iteration's results to be saved in a file Petroleo.dta, the second in GasNat.dta, the third in CarbonMin.dta, etc.

local combustibles "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes"
local n_max: word count `combustibles'
forvalues n = 1/`n_max' {
    local i = 5 + (`n'-1)*30
    local b =  `i'+27
    import excel "Series de oferta y demanda (1).xlsx", cellrange(A`i':AZ`b') firstrow clear
    rename A Country
    foreach v of varlist B-AZ {
        local x : variable label `v'
        rename `v' c_`i'_`b'`x'
        }
    reshape long c_`i'_`b', i(Country) j(year)
    save `:word `n' of `combustibles'', replace
    }

This will at least get each iteration's data into its own file with, I think, an appropriate filename. It still leaves open the question of what you are trying to get for variable names inside those files.

Added: There is another issue with the -rename-ing here. Your -import excel- command includes the -firstrow- option. Consequently, your variables shouldn't even have names like B-CZ in Stata because the variables will have already been renamed to the contents of row `i', unless those cannot be properly used as legal variable names. So I would expect the -rename- command ultimately to tell you that there is no variable B, or the like, to rename. If it does find B, then that means the contents of B1 could not be simply transformed into a legal variable name, in which case the contents of B1 will be stored as the label of variable B, and thereby captured in local macro x. But then the contents of local macro `x' will not be usable in the -rename- command because it cannot be part of a variable name.

So there is much confusion still unresolved about the renaming process. I think you need to provide a lot more information. Because the number of variables is very large, and it is really just the variable names and variable labels that need to be clarified, I suggest that you run just the first iteration of your loop, and then copy the results of -describe- into the Forum editor so we can see those things.

[Sarah Edgington]

What are you intending the new variable names to be?

Stata is giving you an error in your rename syntax because combustibles is a local with spaces and it's possible that the variable label in x is as well, though in this case it appears not to since you say the second syntax runs without error.
So when the macro expands out the line for variable B it would look something like this if we assume the label for that variable is "label"

rename B Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim teslabel

That is almost certainly not what you want, but I don't know from your question what you do want.

[William Lisowski]

Suppose the label of the variable B is "Hello". Then the first time through your loop the command

rename `v' `combustibles'`x'

will expand to

rename B Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tesHello

which is not what you want..

I also find it difficult to believe that the save command in your second example did what you expected.

[Sebastian Kruk]

In my excel I have info about supply of primary energy: oil, natural gas, hydroelectricity, geothermal, nuclear energy, firewood, sugarcane and derivatives, other primary and total primary energy.

Theres one block by source of primary energy with twoway info, countries in rows and years in columns. Every block of source of primary energy is in wide format and I would like to transform to long format to then save it.

For example oil block is between A5:AZ32, natural gas is between A35:AZ62 and so on.

If I do it without a loop by primary energy source:

import excel "Series de oferta y demanda (1).xlsx", cellrange(A5:AZ62) firstrow clear
rename A Country
foreach v of varlist B-AZ {
local x : variable label `v'
rename `v' petroleo`x'
}
reshape long petroleo, i(Country) j(year)
save GasNat, replace

import excel "Series de oferta y demanda (1).xlsx", cellrange(A35:AZ62) firstrow clear
rename A Country
foreach v of varlist B-AZ {
local x : variable label `v'
rename `v' GasNat`x'
}
reshape long GasNat, i(Country) j(year)
save GasNat, replace

.......

import excel "Series de oferta y demanda (1).xlsx", cellrange(A275:AZ302) firstrow clear
rename A Country
foreach v of varlist B-AZ {
local x : variable label `v'
rename `v' tes`x'
}
reshape long tes, i(Country) j(year)
save tes, replace

[Clyde Schechter]

Thank you for the clearer explanation. Your blocks of data are headed, in columns B-AZ are headed by numbers, namely years. So this all hangs together now.

The following code, a slight markup of what I proposed in #2 will loop over the different energy sources and produce the results that you would get from doing each energy source separately as in #5.

local combustibles "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes"
local n_max: word count `combustibles'
forvalues n = 1/`n_max' {
    local energy_source: word `n' of `combustibles'
    local i = 5 + (`n'-1)*30
    local b =  `i'+27
    import excel "Series de oferta y demanda (1).xlsx", cellrange(A`i':AZ`b') firstrow clear
    rename A Country
    foreach v of varlist B-AZ {
        local x : variable label `v'
        rename `v' `energy_source'`x'
    }
    reshape long `energy_source', i(Country) j(year)
    save `energy_source', replace
}

[Sebastian Kruk]

Thank you for the clearer explanation. Your blocks of data are headed, in columns B-AZ are headed by numbers, namely years. So this all hangs together now.

The following code, a slight markup of what I proposed in #2 will loop over the different energy sources and produce the results that you would get from doing each energy source separately as in #5.

local combustibles "Petroleo GasNat CarbonMin Hidroenergia Geotermica Nuclear Leña CañaDeriv OtrasPrim tes"
local n_max: word count `combustibles'
forvalues n = 1/`n_max' {
local energy_source: word `n' of `combustibles'
local i = 5 + (`n'-1)*30
local b = `i'+27
import excel "Series de oferta y demanda (1).xlsx", cellrange(A`i':AZ`b') firstrow clear
rename A Country
foreach v of varlist B-AZ {
local x : variable label `v'
rename `v' `energy_source'`x'
}
reshape long `energy_source', i(Country) j(year)
save `energy_source', replace
}

You're welcome!

Now I tried to save it in a certain directory but I obtain always the same files's name.

save "C:\Users\zloto\OneDrive\Documentos\tesis\Resultad os\`energy_source'", replace

How I can change it to have all the files in directory "C:\Users\zloto\OneDrive\Documentos\tesis\Resultad os" ?

[Clyde Schechter]

You are being bitten by the backslashes that Windows uses as a path separator. The problem is that, to Stata, the character sequence \` is not understood as path separator followed by opening quote of a local macro. Rather it is treated as a single character ` which is interpreted as the literal character `, not as the start of a local macro reference. There are two solutions:

1. Adequate. Change the \`energy_source' sequence to \\`energy_source'. This will work because \\ is also interpreted by Stata as a single literal backslash character, not as the beginning of an "escape sequence."

2. Much better. Change all the \ characters in the path name to /. This is simpler and more transparent. Also, this way your code will work if you ever port it to Mac or Linux, which use / as the path separator and won't recognize \. Although Windows does not recognize / as a path separator, Stata does, and when running any I/O commands on Windows, it replaces the / characters with \ at the very last minute, just before passing the command on to the operating system. In particular, it does that after `energy_source' has been replaced by Petroleo (etc.), so things come out right.

And keep that in mind for the future, because if you remain a Stata user you will encounter this situation repeatedly. I recommend always using / as the path separator in Stata code, regardless of which operating system you are working on.

[William Lisowski]

A minor comment on an unimportant part of post #8.

Although Windows does not recognize / as a path separator, ...

I am not a Windows user and thus not in a position to confirm the following empirically. The following is in my notes from some time ago, unfortunately without a citation to the reference from which I learned it.

My understanding is that every version of Windows has accepted "/" as a path separator, and every version of MS-DOS beginning with DOS 2.0 (the first version that had subdirectories). Only in command lines was "/" not allowed, because it had already been used as a switch delimiter in MS-DOS 1.0.

[Clyde Schechter]

Re #9: I never knew that. But I have just confirmed that in my Windows 10, you can, indeed, use / as a path separator.

[Sebastian Kruk]

Thanks Clyde and William. I am going to use \. See you!