Trouble exporting disaggregated values and variable names/labels with putexcel

[Joe Millward]

Hello,

I apologize if there is any information missing or ways that my question could be better explained. This is my first post to Statalist, though the forum has saved me many headaches over the years.

A goal on one of my current projects is to export values from svy: mean to Excel. The complication here is that I wish to present both total and disaggregated values. To do so, I had hoped to have 3 columns populated in an excel document: Outcome (column A), Disaggregate level (column B), and Value (column C).


The Excel output I am trying to achieve would look something like this (repeated for multiple outcomes of interest):

Outcomes	Disaggregate Level	Value
usenet_weekly	Male	.46
usenet_weekly	Female	.65
usenet_weekly	Poorest	.37
usenet_weekly	Poorer	.43
usenet_weekly	Middle	.56
usenet_weekly	Richer	.65
usenet_weekly	Richest	.78
usenet_weekly	None	.66
usenet_weekly	Primary	.68
usenet_weekly	Secondary or Higher	.73
q213	...	...
q213	...	...

NOTE: numbers in "Value" column were made up for the sake of visualizing the desired output.

I am using Stata 17 on Mac.


Dataex code is available here:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte hh_resid float weight_ind long hh_ea float educ byte wealthquintile float(male usenet_week) byte q213
2  7683.748 30104085 2 3 0 0 0
2  5976.249 30104085 1 3 1 0 0
2  7683.748 30104149 1 2 0 1 0
2  5976.249 30104149 3 2 1 1 0
2  7683.748 30108014 1 4 0 0 0
2  7683.748 30108014 1 4 0 1 0
2  7683.748 30211017 2 2 0 0 0
1  7683.748 31434009 3 3 0 1 0
1  7683.748 31440015 3 4 0 1 0
2  3195.055 30104085 1 3 0 1 0
2  3195.055 30104107 3 2 0 1 0
1  3195.055 30120744 3 5 0 1 1
2  3195.055 30210032 1 3 0 1 0
2  3195.055 30211017 3 5 0 1 1
2  3195.055 30307058 3 4 0 1 0
2  3195.055 30310002 3 1 0 1 0
2  2614.136 30310002 3 1 1 1 1
2  3195.055 31206001 3 5 0 1 1
2  3195.055 31206001 3 5 0 0 0
2 4343.9707 30108014 1 1 0 1 0
2  3800.974 30108014 1 1 1 1 1
2  3800.974 30201060 1 2 1 1 0
2 4343.9707 30201060 1 2 0 1 0
2 4343.9707 30301015 1 1 0 1 0
2 4343.9707 30307801 3 5 0 0 1
2 4343.9707 31201065 1 2 0 1 0
2 4343.9707 31202066 2 2 0 1 0
2 4343.9707 31208019 3 5 0 1 1
2  3800.974 31208019 3 5 1 1 0
2  3540.414 30104085 3 1 0 1 0
2  3540.414 30302076 1 4 0 1 0
2  3540.414 30305019 3 3 1 1 0
2  3540.414 30305019 3 3 0 1 0
1  3540.414 30620708 1 2 0 1 0
1  3540.414 30620708 1 2 1 1 0
2  3540.414 31202066 1 1 0 1 0
2  3540.414 31206001 3 3 0 0 0
2 4564.3594 30201060 1 2 0 1 0
2 4564.3594 30209028 3 3 0 1 1
2 4564.3594 30304024 3 5 0 1 0
2 4564.3594 30304024 3 5 0 1 0
2 3651.4875 30304024 3 5 1 0 0
2  7302.804 30104085 3 4 0 1 0
2  7302.804 30301052 2 5 0 1 1
2  7302.804 31202066 1 3 0 0 0
2  7302.804 31208019 1 4 0 1 0
1  6389.954 31220730 1 4 1 1 0
1  7302.804 31220730 3 4 0 0 1
2  7302.804 31220730 1 4 0 1 1
1  7302.804 31220730 1 4 0 1 0
2  9501.886 30108014 3 2 0 1 0
2  6334.591 30209028 3 4 1 1 1
2  9501.886 30209028 1 4 0 1 1
2  9501.886 30209028 1 4 0 1 1
2  9501.886 30213001 1 2 0 1 0
2  9501.886 30302076 1 1 0 1 0
2  6334.591 30307801 1 5 1 0 0
2  9501.886 30307801 1 5 0 1 0
1  9501.886 30620708 1 1 0 1 0
2  9501.886 31202066 1 2 0 0 0
2  6334.591 31202066 1 2 1 0 0
2  8182.567 30104103 1 1 0 1 0
2  8182.567 30104107 3 2 0 0 0
1  8182.567 30120744 1 5 0 0 0
2  8182.567 30201060 2 1 0 1 0
2  8182.567 30310002 1 3 0 1 0
2  8182.567 31206001 1 3 0 0 1
2  7159.747 31206001 1 3 1 0 1
2  8182.567 31208019 2 2 0 1 0
2  4620.703 30104149 1 2 0 1 0
1  4620.703 30120713 1 3 0 1 0
2  4620.703 30210032 2 3 0 1 0
2  4620.703 30301015 3 5 0 1 0
2  4620.703 30301052 1 3 0 1 0
2  4620.703 31206001 3 5 1 1 0
2  4620.703 31206001 3 5 0 0 1
1  4620.703 31440015 3 5 0 1 0
2   3046.08 30211017 1 1 0 1 0
2   3046.08 30305019 2 3 0 1 0
2   3046.08 31202066 1 2 0 0 0
2   3046.08 31202066 3 2 1 0 0
2   3046.08 31208019 1 1 0 1 0
2   3046.08 31208019 1 1 0 0 0
2  5427.533 30105023 1 1 0 0 0
2  5427.533 30105023 1 1 1 0 0
2  5427.533 30105031 1 1 0 1 0
2  5427.533 30214011 1 3 1 1 0
2  5427.533 30214011 1 3 0 0 1
2  5427.533 30301015 1 5 0 1 0
2  5427.533 30301015 3 5 1 1 1
2  3540.414 30302076 3 3 0 1 0
2 2098.1626 30104103 1 4 0 1 0
2 2098.1626 30104103 3 4 1 1 1
2 2098.1626 30210032 1 1 0 0 0
2 2098.1626 30210032 2 1 1 1 1
2 2098.1626 30211017 1 2 0 1 1
2 2098.1626 30211017 1 2 1 1 0
1 2098.1626 31220730 1 2 0 1 0
2  6334.591 30104107 3 2 1 1 0
2  6334.591 30104107 3 2 0 1 0
end
label values hh_resid resid
label def resid 1 "Urban", modify
label def resid 2 "Rural", modify
label values educ educ
label def educ 1 "None", modify
label def educ 2 "Primary", modify
label def educ 3 "Secondary or higher", modify
label values wealthquintile quint
label def quint 1 "Poorest", modify
label def quint 2 "Poorer", modify
label def quint 3 "Middle", modify
label def quint 4 "Richer", modify
label def quint 5 "Richest", modify
label values male male
label def male 0 "Female", modify
label def male 1 "Male", modify
label values q213 yn
label def yn 0 "No", modify
label def yn 1 "Yes", modify

The code I am trying to run is below:

local vectorcontrol usenet_week q213
local demographicvars male educ wealthquintile


label variable usenet_week "Weekly Net Use"

svyset hh_ea  [pweight=weight_ind], strata(hh_resid)

////// Exporting results
    
    ****** LogU5M_hat by country
    
putexcel set "/Results.xlsx", sheet(Vector Control) modify // note: "cd" command precedes listed code

putexcel A1 = "Outcome"
putexcel B1 = "Disaggregate level"
putexcel C1 = "Value"

local row = 2

foreach i of local `vectorcontrol'{
    foreach x of local `demographicvars'{
        levelsof `x', local(``x'lvl'')
        foreach y of local ``x'lvl'{
            
            desc `i'
            local varname : var label `i'
            putexcel A`row' = ("`varname'")

            desc `x'lvl
            local varlevel : var label `x'lvl
            putexcel B`row' = ("`varlevel'")
            
            svy: mean `i'
            putexcel C`row' = e(b)
            
            local row = `row' + 1
        }
    }
}

I think that the trouble is coming from where I try to create the `x'lvl locals. Should I be using "by" somewhere within this loop instead? Should I write loops separately for each variable in `demographicvar'? Note sure if I may also be stretching the capacity of local() by asking it to create multiple local macros based on levels of variables in the `demographicvars' macro. Any suggestions besides using the putexcel command are also very much appreciated.

Thank you in advance for taking the time to read this, and I look forward to any advice that you may have. I also apologize in advance if I am missing something extremely obvious.

Thank you again,
Joe

[Roman Mostazir]

You have several problems in your codes i.e., use of quotes, refering to macros, increments are some to name. Try the following, I also added 95% CIs in case if you need it. You can delete that part if unnecessary:

putexcel set "/Results.xlsx", sheet(Vector Control) modify

putexcel A1 = "Outcome"
putexcel B1 = "Disaggregate level"
putexcel C1 = "Value"


local row = 2

local vectorcontrol usenet_week q213
local demographicvars male educ wealthquintile


foreach i of local vectorcontrol {
   
   foreach x of local demographicvars {
    
    levelsof `x', local(levels)
     loc t :  val lab `x'
        
        foreach l of local levels {
            loc lb : lab `t' `l'
           
            di "Mean for `x'=`lb'"
            di _dup(50) "*"
    
            svy : mean `i' if `x' == `l'
            
            mat k = r(table)
            loc m     : di %8.2f k[1,1]
            loc cil : di %8.2f k[5,1]
            loc ciu : di %8.2f k[6,1]
            
            putexcel A`row' = ("`x'")
            putexcel B`row' = ("`lb'")
           
            putexcel C`row' = "`m' (`cil'–`ciu')"
            
            local ++row
        }
    }
}

[Joe Millward]

Hello Roman,

Thank you so much for this solution. It works beautifully. I have added one slight modification (for others who may be interested in these solutions) to export the outcome variable names to column A (as below), rather than the names of the demographic variables.

I greatly appreciate your support, and hope that you are having a great year so far!

foreach i of local vectorcontrol {
   
   foreach x of local demographicvars {
    
    levelsof `x', local(levels)
     loc t :  val lab `x'
        
        foreach l of local levels {
            loc lb : lab `t' `l'
           
            di "Mean for `x'=`lb'"
            di _dup(50) "*"
    
            svy : mean `i' if `x' == `l'
            
            mat k = r(table)
            loc m     : di %8.2f k[1,1]
            loc cil : di %8.2f k[5,1]
            loc ciu : di %8.2f k[6,1]
            
            putexcel A`row' = ("`i'")
            putexcel B`row' = ("`lb'")
           
            putexcel C`row' = "`m' (`cil'–`ciu')"
            
            local ++row
        }
    }
}

-Joe