Import excel issue

[Euslaner]

Greetings,

I have an Excel data set where the first row is years and the second row is the variable name (file attached). I know how to use the firstrow option in import excel, but this data set has two rows of variable names. Any help would be appreciated. Thanks,

Ric

[Nick Cox]

The data proper start at line 3 in the worksheet. I don't think there is a clean solution in cases like this: your variable names and labels will require some editing after you have read the data into Stata. It would be nice to be wrong on that.

[Friedrich Huebler]

This isn't as complicated as Nick implied.

set more off

* Import the data
import excel using "wbvoice.xlsx", clear

* Rename the first two variables
ren A country
ren B wbcode

* Rename the remaining variables using the information in rows 1 and 2
foreach var of varlist C - CT {
  local year = `var'[1]
  local value = `var'[2]
  ren `var' `value'`year'
}

* Drop the observations that were used to rename the variables
drop in 1/2

* Convert strings to numeric variables
foreach var of varlist Estimate1996 - Upper2014 {
  destring `var', replace
}

* Optional: reshape the data from wide to long
reshape long Estimate StdErr NumSrc Rank Lower Upper, i(country wbcode) j(year)

[Nick Cox]

It is nice to be wrong on that.

A minute tweak is that there is no need to call destring in a loop:

destring Estimate1996 - Upper2014, replace

[Friedrich Huebler]

Thank you for the destring correction.

This problem reminded me of an exchange Nick and I had a few years ago.

http://www.stata.com/statalist/archi.../msg00920.html