Export Summary Stats with variable labels using putexcel

[Chrisk Burningham]

Hi Statalisters!

I am trying to make the most of stata16's new automated reporting functions, and playing around with generating summary statistics. I'm currently using the following code but struggling figuring out how to use the variable labels rather than variables themselves

putexcel set report.xlsx, replace 

putexcel A1= "Sum Test"
putexcel A1:F1, border(bottom) merge hcenter
tabstat age height weight, stat(n mean sd min max) save 
return list
putexcel A2= matrix(r(StatTotal)'), names nformat(number_d2)

This is an ideal way of exporting summary stats, as I can integrate them into specific parts of the .xlsx file. What I'm struggling with is how to use variable labels instead of the variables themselves. I can do this with estpost, but then I can only export as a csv.

Any help would be really useful!
Thanks in advance.

Chris Burningham

[Abass Babatunde]

Hi Chris,

Thank you for bringing this up and I am sorry I do not have an answer to your question but I want you to clarify this for me because its exactly what I am currently working on.
I am trying to put my summary table into an excel spreadsheet. I have been reading about putexcel but with a little success. I got how to format and set the first row or my headings but putting in the statistic figures in the rows and column like the atrices have been eluding me.
Can you explain what you mean in row A2, what r and stattotal means.

Thanks

[Chrisk Burningham]

Hi Abass,

Yes, tabstat generates the summary statistics we are interested in, when you run this command, certain information is stored locally. By typing return list you'll get the list of all thats stored. For tabstat, just the matrix itself is stored, and it is stored in r(StatTotal).
putexcel A2= matrix(r(StatTotal)') // A2 is the cell in which I want the output to start from. r(StatTotal) is the matrix stored locally. Finnaly ' transposes the results

[Chrisk Burningham]

Of course, I would be incredibly grateful if someone could explain how I could report the value labels instead!

Warmly,
Chris

[Andrew Musau]

A big issue with your request is the lack of a reproducible example. Luckily, I can recall that the Stata dataset nhanes2f has the exact variables as in your code example in #1. There is no option to get the variable labels directly, but since you know what cells in Excel these are supposed to be in, you can extract the labels and overwrite the variable names.

webuse nhanes2f, clear
putexcel set report.xlsx, replace
putexcel A1= "Sum Test"
putexcel A1:F1, border(bottom) merge hcenter
tabstat age height weight, stat(n mean sd min max) save
return list
putexcel A2= matrix(r(StatTotal)'), names nformat(number_d2)

*If column headings start at 2, var names start at 3
local i 3
foreach var in age height weight{
    local label: var lab `var'
    putexcel A`i' = "`label'"
    local ++i
}

Res.:

. import excel report, clear
(6 vars, 5 obs)

. l

     +------------------------------------------------------------------------------------------------------+
     |            A       B                   C                   D                   E                   F |
     |------------------------------------------------------------------------------------------------------|
  1. |     Sum Test                                                                                         |
  2. |                    N                mean                  sd                 min                 max |
  3. | age in years   10337   47.56370320208958   17.21677685646228                  20                  74 |
  4. |  height (cm)   10337   167.6512457460202   9.660011674081908               135.5                 200 |
  5. |  weight (kg)   10337   71.90088127249155   15.35514572415323   30.84000015258789   175.8800048828125 |
     +------------------------------------------------------------------------------------------------------+

[Chrisk Burningham]

I see,

That looks great - thanks for the straight forward solution!