Stata: rename variables in lowercase when importing from Excel

Question

I'm trying to import an Excel sheet that has variable names which have a mix of uppercase and lowercase letters. I want Stata to read them all as lower case but when I put the case(lower) option in, Stata returns option case() not allowed.

Here is what I try to put in:

import excel "/Users/p/D/B/File name/", sheet("Sheet1") firstrow case(lower)

The import command works fine when I don't use the case() option. Can someone tell me what I'm doing wrong?

Does your file name actually end with a forward slash? I have never seen this. What Stata release are you using? Import excel is a new thing. It looks also like you are using a Mac? So many aspects to the question. Otherwise, first thing to do would be to turn on trace and look at precisely where the error is happening. Or post your file and the real code that is and isn't working for you, and we can help better. — SOConnell
You have not given us a reproducible example and no-one trying independently has been able to devise one. — Nick Cox

Roberto Ferrer Roberto Ferrer · Accepted Answer · 2014-01-15T04:46:21

I find it difficult to reproduce your problem, but below is a working example using various case() options (basically from help import excel):

clear all
set more off


*----------------- Create data -----------------------------

* Load example Stata data base
sysuse auto
keep make price headroom length turn

* Change variable names to experiment
rename (headroom length turn) (HEADRoom LENGTH turN)

* Create example Excel file
export excel auto, firstrow(variables) replace


*----------------- Importing -------------------------------

* Import just as it is
import excel auto.xls, firstrow clear
describe

* Import with upper case
import excel auto.xls, firstrow case(upper) clear
describe

* Import with lower case
import excel auto.xls, firstrow case(lower) clear
describe

Note they all work fine. Variable names are converted to upper/lower case accordingly. Variable labels are not, but this is expected. From help import excel we have:

The original names in the first row are stored unmodified as variable labels.

and case() only affects variable names.

You also mention:

The import command works fine when I don't use the case() option.

Suppose this is true. Then to convert variable names to lower case after the import, you can try

rename _all, lower

See help rename group for details. (But like I showed, the case() option works.)

Finally, if it is the variable labels you are after, you can change them using something like

foreach varr of varlist _all {
    label variable `varr' "`varr'"
}

That just copies the variable name to the label, for all variables. So running that after all your variable names are converted to lower case, would give you variable labels that are also lower case. Run help label for details.

Stata: rename variables in lowercase when importing from Excel

1 Answers