How to get the value labels of variables in a loop with putexcel command?

[Michael Duarte Goncalves]

Hi, I recently used a loop in a command called putexcel. However, I would like to retrieve the value labels of one of my variables. Here is the variable in question:

 
activites_actuelles
Freq.
Percent
Cum.




Autres activités
1,127
3.36
3.36

En emploi
18,291
54.52
57.88

En recherche d'emploi
65
0.19
58.07

En études
9,772
29.13
87.20

En études et en emploi
4,184
12.47
99.68

En études et en recherche
109
0.32
100.00





Total
33,548
100.00

and the code used:

putexcel set Figures.xlsx, sheet(Figure 2) modify
// help putexcel
putexcel A2 = "Variables"
putexcel B2 = "Variables Names"
*
local row = 3
*
*
// loop for Bachelor_hes only
    forvalues i = 1/3    {
    
        local ba_hes Bachelor_hes
            foreach c of local ba_hes    {
                svy,subpop(`c'`i'): prop activites_actuelles
                matrix A = r(table)'
                matrix B = e(_N_subp)'
                local varlabel1  : var label activites_actuelles
                local varlabel2  : var label Bachelor_hes`i'
                putexcel A`row'  = "`varlabel1'"
                putexcel B`row'  = matrix(A)
                putexcel K`row'  = matrix(B)
                putexcel L`row'  = "`varlabel2'"
                local row = `row' + rowsof(A)    
        }
    }

However, nothing shows up as "expected" in column A when I look at my Excel sheet.

Are there any suggestions?
Thank you.

Michael

[Mike Lacy]

First of all, it sounds to me like you might have some confusion about value labels vs. variable labels. You ask about the former, but your code only extracts variable labels. You might clarify that for yourself and take another look at -help extended_fcn-. If you really do want to extract the value labels for the values of a given variable, you'll find from there that you need to do something like the following. (I've used a built in Stata example data set rather than yours because you didn't offer a -dataex- extract.

sysuse lifeexp
// Extract variable and value label for one variable contained in a macro
local onevar "region"
local varlabel: var label `onevar'
local labname : value label `onevar'
di "name = `onevar', label = `varlabel'"
// Get list of values and display the labels for each one
levelsof `onevar', local(vlist)
foreach num of local vlist {
    local VL: label `labname' `num'
    di "Value label for `num': `VL'"
}

If your issue really is just about variable labels, as your code rather than your posting suggests, my suspicion would be that your locals variablelabel1 and variablelabel2 don't have any content. There are various reasons this might happen. With a -dataex- extract, it would be easy enough to figure out your problem, but without that, I'd guess there is some issue with the contents of your Bachelor_hes and ba_he, which of course I don't know. You might try diagnosing this yourself by inserting a few -display- commands in your loop to show the actual contents of your various locals. Finally, if your "nothing" means that absolutely nothing (not even column headers) showed up in your Excel file, there might be problems with your use of -putexcel-, but I don't have much experience with that command.

[Michael Duarte Goncalves]

Hello Mike Lacy. Thanks a lot for your help.

I apologize for not having given an example previously.
Sorry for my bad explanation.

This is exactly the code I needed :

 sysuse lifeexp
// Extract variable and value label for one variable contained in a macro
local onevar "region"
local varlabel: var label `onevar'
local labname : value label `onevar'
di "name = `onevar', label = `varlabel'"
// Get list of values and display the labels for each one
levelsof `onevar', local(vlist)
foreach num of local vlist {    
local VL: label `labname' `num'
 di "Value label for `num': `VL'"
}

Thanks again for your help.

Michael

[daniel klein]

Note that the approach using levelsof assumes that all values are observed in the data. elabel (SSC) obtains the values (and labels) from the value labels directly. Thus, an alternative to

levelsof `onevar', local(vlist)

is

elabel list (`onevar')
local vlis `r(values)'

[Michael Duarte Goncalves]

Hello daniel klein :

Thank you for your suggestion.
After testing, I really like the elabel command.


Best,

Michael

[Michael Duarte Goncalves]

Dear daniel klein :

I have one more question. I'm trying to extract the value labels of `activites_now' to an Excel sheet. This variable is coded from 1 to 6, with corresponding value labels.

putexcel set Fig_test.xlsx, sheet(Figure 6) modify
putexcel A1 = "Variables Labels"
putexcel B1 = "Proportions"
putexcel K1 = "N"
putexcel L1 = "Diploma Labels"
putexcel M1 = "Variable Value Labels"
local row = 2
    forvalues i=1/3{
        local ba_hes Bachelor_hes
        foreach c of local ba_hes    {
                svy,subpop(`c'`i'): prop activites_now ,over(restricted_field)
                matrix B = r(table)'
                matrix C = e(_N_subp)'
                elabel list (`activites_now')
                local vlis `r(labels)'
                local varlabel1  : var label wasgeink
                local varlabel2  : var label Bachelor_hes`i'
                putexcel A`row'  = "`varlabel1'"
                putexcel B`row'  = matrix(B)
                putexcel K`row'  = matrix(C)
                putexcel L`row'  = "`varlabel2'"
                putexcel M`row'  = "`vlis'"
                local row = `row' + rowsof(B)    
    }
}

But I obtain the following error:

"EN EMPLOI": invalid cell name
r(198);

Could you help me please?

Thank you so much in advance.

[daniel klein]

The line

elabel list (`activites_now1')

expands to

elabel list ()

because the local activities_now1 is never defined in the code. Perhaps you wanted

elabel list (activities_now)

Note that the code is inefficient and harder to read than necessary at various places. For example,

local ba_hes Bachelor_hes
foreach c of local ba_hes {
...
}

could just be

foreach c in Bachelor_hes {
...

or be omitted altogether because c only takes on one value.

Also, the call to elabel, if it is meant for activities_now, is unnecessarily repeated. It could/should be placed outside of the loop because it depends neither on local c nor i.

[Michael Duarte Goncalves]

Thank you very much for your quick response.

I'll test what you have written in post #7 and see what happens.

Thank you also for the suggestions to improve the code. They are always welcome, especially since I am a youngster in the use of Stata

Michael