How to bulk assign variable labels from Excel sheet

[Margaux Cameron]

Hi all,

I have a dataset with over 100 variables, none of which have labels. I also have an Excel spreadsheet ("Excel") with the 100+ variable names in one column ("Variable") & the appropriate variable labels (which are multi-word phrases) in another column ("Label"). This is how these data were given to me. I need to label all the variables in my Stata dataset. How can I do this using the Excel spreadsheet?

First I tried:

import excel "Excel", sheet("var_labels") firstrow clear
levelsof Label, loc(labels)

use $dataset, clear

ds _all
tokenize `r(varlist)'
foreach x of local labels {
    lab var `1' "`x'"
    macro shift
    }

The tokenize section worked great, but the labels were assigned in the wrong order. The local created by the levelsof command lists the labels alphabetically, so the wrong labels ended up being applied to the variables.

I then tried to create a local with the labels in the correct order:

import excel "Excel", sheet("var_labels") firstrow clear
local labels
local end = r(N)
forval i = 1/`end' {
    levelsof Label if _n == `i', loc(lab`i')
    local labels `labels' `lab`i''
    }

This worked except for the first label, which didn't have quotation marks around it & so broke my code when I tried to apply the variable labels since Stata didn't recognize it as the full phrase.

I am stumped. I've read a lot of the forum topics that seem like the same problem (like the one I linked) but can't get them to work for me. I don't know how to use Mata so would like to avoid that if possible. Could someone please help me figure this out? Thanks!

[Andrew Musau]

Consider the approach using merge detailed in https://www.statalist.org/forums/for...v-lookup-table.

[eric_a_booth]

I have a working ado that is conceptually similar to the way -descsave- from SSC exports a dofile that will allow you to relabel / reformat data via the do-file it exports (but going the other direction to import csv/excel and re-label it from it's metadata or codebook export).
My repo has some worked examples that deal with complications I've encountered in case they are helpful illustration.
You can install the ado and the test do-file worked examples via the command below:

net install applyvarlabels, from("https://raw.githubusercontent.com/ericabooth/applyvarlabels-stata-public/main/") replace force
help applyvarlabels
net get applyvarlabels, from("https://raw.githubusercontent.com/ericabooth/applyvarlabels-stata-public/main/")
do example_applyvarlabels.do

via my github: https://github.com/ericabooth/applyv...s-stata-public

[Ken Chui]

After importing the Excel, I'd make the command there to ensure they are matched:

gen com = "label variable " + variable + " " + char(34) + label + char(34)
levelsof com, loc(labeling)

Then, load the actual data, and simply:

foreach x in `labeling'{
    `x'
}

[Margaux Cameron]

Consider the approach using merge detailed in https://www.statalist.org/forums/for...v-lookup-table.

THANK YOU!! This is perfect. I've never merged on _n before! A fun solution.

[Mike Lacy]

I've often had lists of variable and labels like this created in a spreadsheet by a non-Stata user, and I've generally accomplished the task here by using the spreadsheet's own commands (CONCAT, in Excel) to create Stata syntax. I just tried Ken Chui 's pure Stata approach, which I liked. I wondered if there might be a problem using it with a large number of variables and labels because it would make -levelsof- handle a variable with a lot of values, which beyond creating a local with many characters, could be excessive for reasons not documented or anticipated. I tried Ken's approach with Stata SE on a file with 600+ variable and their labels --- no problems. Of course, one could alternatively use syntax like Ken's to write a text do-file with each "label variable ..." command in it, but this is nicer.

[Ken Chui]

Mike Lacy, maybe a bit "hacky" but it's possible to directly export the command column as a do file if number of variables eventually got too big for levelsof():

gen com = "label variable " + variable + " " + char(34) + label + char(34)
keep com
outfile using "label_code.do", noquote replace wide

* Open the actual data set

do label_code

[Mike Lacy]

Ken, this is the same as what I'd do. I don't think of it as a hack; in fact, it is arguably a more reproducible and corrigible way to do things, if, for example, you change your mind about the label text you like.