Export results from tabulate command with survey weights to excel (preferably with putexcel)

[Katrina Blindow]

Hi everyone,

I am trying to neatly export the results from this expression to excel, preferably with the putexcel command, so that I can add details from within stata.

svy: tabulate h33117_2_3c XALTER_3c, col

Eventually the h33117_* variables will be replaced by globals and I will loop through several variables, saving results below each other. And I will also change the XALTER_3c for other variables and save it in different sheets.

When I exported regression results with putexcel, this could be done very comfortably. So, I would like to do the same, but this time I cannot seem to get the results, I assume it has to do with the weights. I am aiming for something like this (except I am aware, there is no matrix saved). The output needs to be percent of h33117_* within each group of XALTER_3c. I don't care if this is done with a different command than tabulate, but all other commands I tried so far, did not give me that output or were not compatible with weights.

putexcel set ${OUTPUT}Impulse2025, sheet(Alter) replace

local row = 1

foreach var in $h33117vars {

local varlabel : variable label `var'
putexcel A`row' = "`varlabel'", bold

local row = `row' + 1
svy: tabulate `var'_3c XALTER_3c, col

putexcel A`row' = matrix(r)

local row = `row' + 10
}

Thank you for your help!

Katrina

[Andrew Musau]

I would use dtable instead of tabulate for this. Then you can append the resulting tables and export using collect export, see

help collect export

Note that enclosing a reproducible example increases your chances of getting helpful replies.

webuse svy_tabopt, clear
svyset psuid [pweight=finalwgt], strata(stratid)
svy: tabulate gender race, col
dtable i.gender, by(race) svy factor(, statistics(fvproportion))

Res.:

. 
. svy: tabulate gender race, col
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,351
Number of PSUs   = 62                            Population size = 117,157,513
                                                 Design df       =          31

--------------------------------------
          |            Race           
   Gender | White  Black  Other  Total
----------+---------------------------
     Male | .4806  .4554   .525  .4794
   Female | .5194  .5446   .475  .5206
          | 
    Total |     1      1      1      1
--------------------------------------
Key: Column proportion

  Pearson:
    Uncorrected   chi2(2)         =    4.5159
    Design-based  F(1.93, 59.72)  =    1.2442     P = 0.2946

. 
. dtable i.gender, by(race) svy factor(, statistics(fvproportion))

------------------------------------------------------------------------------------
                                             Race                                   
                White              Black             Other              Total       
------------------------------------------------------------------------------------
N        102,999,549 (87.9%) 11,189,236 (9.6%) 2,968,728 (2.5%) 117,157,513 (100.0%)
Gender                                                                              
  Male                 0.481             0.455            0.525                0.479
  Female               0.519             0.545            0.475                0.521
------------------------------------------------------------------------------------

[Simon Pfaff]

I addition to the example of Andrew Musau, I used putexcel, to export the table to Excel.

* previous example by Andrew Musau
webuse svy_tabopt, clear
svyset psuid [pweight=finalwgt], strata(stratid)
svy: tabulate gender race, col
dtable i.gender, by(race) svy factor(, statistics(fvproportion))

* Show the created table/collection    
collect dir    

* insert the table using putexcel
putexcel set result.xlsx, sheet("1") modify
putexcel A1 = collect, name(DTable)

* change col width
mata: b = xl()
mata: b.load_book("result.xlsx")
mata: b.set_sheet("1")
mata: b.set_column_width(1,1,30)

* open excel from within stata
di as smcl "open excel: " `"{stata "!result.xlsx"}"'