Importing excel with same name variables

[Zoe Packman]

Writing a simple script that opens a series of Excel sheets in a folder, reshapes from wide to long on two variables, and outputs a text file in long form.

The roadblock I'm hitting is that the two variable types in Excel all share the same name (for example, Variant and Rate) so when imported into Stata, all of the "Variant" variables are converted to A, C, E, G, I... and all of the "Rate" variables are converted to B, D, F, H... However, the variables remain "Variant" and "Rate" for each.

The files all differ in the number of Variant and Rate variables as well, ranging from 1 of each to 20+ of each.

Ideally, I would like to change all of the A, C, E, G variable names into Variant1, Variant2, etc. so that I can call the stubs in reshape. Is there a way to use the label name to call the variable so that I could simply rename all variables with label "Variant"?

[Nick Cox]

It's programmable. Whether a couple of simple renames would work just as well is not clear.

Code here not tested much, but works for A possibly as far as Z.

* sandbox
clear
set obs 1
foreach v in `c(ALPHA)' {
    gen `v' = 42
}

ds

* code
tokenize `c(ALPHA)'

mac li  

local k = 0
forval j = 1/26 {
    capture confirm var ``j''
    if _rc break
    else  {
         if mod(`j', 2) {
            local ++k
            rename ``j'' Variant`k'
        }
        else rename ``j'' Rate`k'
    }
}

ds