Creating descriptive stats table using putdocx

[Jill Caviglia-Harris]

Stata list:

I am trying to create a summary stats table using putdocx that includes the variable names, variable definitions (labels in Stata), observations, mean, sd, max and min and can't’ get the loop to work correctly. When I run the code below, the only variable included in the table is last one (x3 here). All the other values are written over. The resulting table is below.

local variables x1 x2 x3
putdocx table table2 = (3,7), border(all, nil) width(100%) layout(autofitcontents) note(Note: The table notes can be found here.)
putdocx table table2(1,1)=("Summary Statistics"), bold font("Calibri", 12) halign(center) colspan(7) linebreak
putdocx table table2(2,1) = ("Variable")
putdocx table table2(2,2) = ("Variable Definition")
putdocx table table2(2,3) = ("Obs.")
putdocx table table2(2,4) = ("Mean")
putdocx table table2(2,5) = ("Std. Dev.")
putdocx table table2(2,6) = ("Min.")
putdocx table table2(2,7) = ("Max.")

foreach var of varlist `variables' {
local lab: variable label `var'
local nvars: word count `var'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 3: one for the header, one for the footer (notes) and the other for the table title */
quietly summarize `var'
local i=3
while `i'<=`nrows' {
putdocx table table2(`i',1) = ("`var'")
putdocx table table2(`i',2) = ("`lab'")
putdocx table table2(`i',3) = (r(N))
putdocx table table2(`i',4) = (r(mean)), nformat(%5.2f)
putdocx table table2(`i',5) = (r(sd)), nformat(%5.2f)
putdocx table table2(`i',6) = (r(min))
putdocx table table2(`i',7) = (r(max))
putdocx table table2(`i',.), addrows(1, after)
local i=`i'+1
}
}
putdocx save "$mypath\Reports\ExampleReport9-test", replace
exit

This is the resulting table

Summary Statistics
Variable	Variable Definition	Obs.	Mean	Std. Dev.	Min.	Max.
x3	This is the variable label for x3	980	15.51	21.38	0	150
Note: The table notes can be found here.

When I change the addrows line from

putdocx table table2(`i',.), addrows(1, after) to

putdocx table table2(`i',.), addrows(1, before) I do get all of the variables to be listed in the table but they are added from the bottom up (as expected).

This is the resulting table

Summary Statistics
Variable	Variable Definition	Obs.	Mean	Std. Dev.	Min.	Max.
x3	This is the variable label for x3	980	15.51	21.38	0	150
x2	This is the variable label for x2	1357	13602.91	23750.34	0	184140
x1	This is the variable label for x1	1225	132.88	242.21	0	3850
Note: The table notes can be found here.

Can anyone advise on how to add these from top to bottom and get rid of the empty row on top (before the variables)? Thanks.

[Mike Murphy]

It's a tricky to diagnose without a reproducible example, but as a quick & dirty fix couldn't you just reverse the order of the variables in your local?

local variables x3 x2 x1

Then just use another if statement to condition

putdocx table table2(`i',.), addrows(1, after)

on i < nrows

[Mike Murphy]

But what I think you really want to do is use something like tabstat to create the table first as a matrix, then just export the matrix in one go. Something like:

sysuse auto, clear
putdocx begin
tabstat price mpg, stats(mean sd min max N) save
mat my_matrix = r(StatTotal)'
putdocx table my_table = matrix(my_matrix), rownames colnames
putdocx save my_example, replace

[William Lisowski]

I see the following in the code in post #1.

local variables x1 x2 x3
...
foreach var of varlist `variables' {
...
local nvars: word count `var'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 3: one for the header, one for the footer (notes) and the other for the table title */

I think you wanted

local variables x1 x2 x3
...
foreach var of varlist `variables' {
...
local nvars: word count `variables'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 3: one for the header, one for the footer (notes) and the other for the table title */

[Jill Caviglia-Harris]

Thanks for this suggestion, but I need the variable labels and variable names in the table. Tabstat does not create tables that can include these two items.

But what I think you really want to do is use something like tabstat to create the table first as a matrix, then just export the matrix in one go. Something like:

sysuse auto, clear
putdocx begin
tabstat price mpg, stats(mean sd min max N) save
mat my_matrix = r(StatTotal)'
putdocx table my_table = matrix(my_matrix), rownames colnames
putdocx save my_example, replace

[Jill Caviglia-Harris]

Thanks for this suggestion. I didn't think it would make a difference since the number is 3 in both cases (I use the two locals because I also run a series of graphs in the report and don't want to enter the variable names more than once), but it did. This is the result now:
Summary Statistics

Variable Variable Definition Obs. Mean Std. Dev. Min. Max.
x3 This is the variable label for x3 980 15.51 21.38 0 150
x3 This is the variable label for x3 980 15.51 21.38 0 150
x3 This is the variable label for x3 980 15.51 21.38 0 150

x2 This is the variable label for x2 1357 13602.91 23750.34 0 184140
x2 This is the variable label for x2 1357 13602.91 23750.34 0 184140

x1 This is the variable label for x1 1225 132.88 242.21 0 3850
x1 This is the variable label for x1 1225 132.88 242.21 0 3850

Note: The table notes can be found here.

I see the following in the code in post #1.

local variables x1 x2 x3
...
foreach var of varlist `variables' {
...
local nvars: word count `var'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 3: one for the header, one for the footer (notes) and the other for the table title */

I think you wanted

local variables x1 x2 x3
...
foreach var of varlist `variables' {
...
local nvars: word count `variables'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 3: one for the header, one for the footer (notes) and the other for the table title */

[William Lisowski]

I think you are overlooking the fact that the local var is the index of your foreach loop and thus, on each pass through the loop, will contain just one of the three variable names included in the local variables.

 // original code
. local variables x1 x2 x3

. foreach var of varlist `variables' {
  2. local nvars: word count `var'
  3. local nrows=`nvars'+2 
  4. display
  5. macro list _variables _var _nvars _nrows
  6. }

_variables:     x1 x2 x3
_var:           x1
_nvars:         1
_nrows:         3

_variables:     x1 x2 x3
_var:           x2
_nvars:         1
_nrows:         3

_variables:     x1 x2 x3
_var:           x3
_nvars:         1
_nrows:         3

. // corrected code
. local variables x1 x2 x3

. foreach var of varlist `variables' {
  2. local nvars: word count `variables'
  3. local nrows=`nvars'+2 
  4. display
  5. macro list _variables _var _nvars _nrows
  6. }

_variables:     x1 x2 x3
_var:           x1
_nvars:         3
_nrows:         5

_variables:     x1 x2 x3
_var:           x2
_nvars:         3
_nrows:         5

_variables:     x1 x2 x3
_var:           x3
_nvars:         3
_nrows:         5

.

[William Lisowski]

I created a reproducible example of your problem (as recommended by the Statalist FAQ linked to from the top of the page) and was able to resolve the various problems with the code in post #1.

// create pretend data
set obs 100
set seed 42
generate x1 = rnormal(10,1)
generate x2 = rnormal(20,2)
generate x3 = rnormal(30,3)
label variable x1 "Label for x1"
label variable x2 "Label for x2"
label variable x3 "Label for x3"

// into the code from post #1
local variables x1 x2 x3
local nvars: word count `variables'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 3: one for the header, one for the footer (notes) and the other for the table title */

putdocx begin
putdocx table table2 = (`nrows',7), border(all, nil) width(100%) layout(autofitcontents) note(Note: The table notes can be found here.)
putdocx table table2(1,1)=("Summary Statistics"), bold font("Calibri", 12) halign(center) colspan(7) linebreak
putdocx table table2(2,1) = ("Variable")
putdocx table table2(2,2) = ("Variable Definition")
putdocx table table2(2,3) = ("Obs.")
putdocx table table2(2,4) = ("Mean")
putdocx table table2(2,5) = ("Std. Dev.")
putdocx table table2(2,6) = ("Min.")
putdocx table table2(2,7) = ("Max.")

local i=3

foreach var of varlist `variables' {
    local lab: variable label `var'
    quietly summarize `var'
    putdocx table table2(`i',1) = ("`var'")
    putdocx table table2(`i',2) = ("`lab'")
    putdocx table table2(`i',3) = (r(N))
    putdocx table table2(`i',4) = (r(mean)), nformat(%5.2f)
    putdocx table table2(`i',5) = (r(sd)), nformat(%5.2f)
    putdocx table table2(`i',6) = (r(min))
    putdocx table table2(`i',7) = (r(max))
    local i=`i'+1
}

putdocx save "~/Downloads/report", replace
exit

[Jill Caviglia-Harris]

Ah, yes. You are correct. Thanks! I rearranged the code, moved the locals to the top and now set the table according to the number of rows so that I don't need to add rows later, but can't figure out why the values for the last variable (x3) are listed in each of the rows (instead of the values for x1 x2 and x3) Does anyone have any insights on this? Table results below.

local variables x1 x2 x3
local nvars: word count `variables'
local nrows=`nvars'+2 /* the number of rows in the table is the number of variables + 2: one for the header and one for the table title */
putdocx table table2 = (`nrows',7), border(all, nil) width(100%) layout(autofitcontents) note(Note: The table notes can be found here.)
putdocx table table2(1,1)=("Summary Statistics"), bold font("Calibri", 12) halign(center) colspan(7) linebreak
putdocx table table2(2,1) = ("Variable"), border(bottom)
putdocx table table2(2,2) = ("Variable Definition"), border(bottom)
putdocx table table2(2,3) = ("Obs."), border(bottom)
putdocx table table2(2,4) = ("Mean"), border(bottom)
putdocx table table2(2,5) = ("Std. Dev."), border(bottom)
putdocx table table2(2,6) = ("Min."), border(bottom)
putdocx table table2(2,7) = ("Max."), border(bottom)

foreach var of varlist `variables' {
local lab: variable label `var'
quietly summarize `var'
local i=3
while `i'<=`nrows' {
putdocx table table2(`i',1) = ("`var'")
putdocx table table2(`i',2) = ("`lab'")
putdocx table table2(`i',3) = (r(N))
putdocx table table2(`i',4) = (r(mean)), nformat(%5.2f)
putdocx table table2(`i',5) = (r(sd)), nformat(%5.2f)
putdocx table table2(`i',6) = (r(min))
putdocx table table2(`i',7) = (r(max))
local i=`i'+1
}
}
Resulting Table:

Summary Statistics
Variable	Variable Definition	Obs.	Mean	Std. Dev.	Min.	Max.
x3	This is the variable label for x3	980	15.51	21.38	0	150
x3	This is the variable label for x3	980	15.51	21.38	0	150
x3	This is the variable label for x3	980	15.51	21.38	0	150
Note: The table notes can be found here.

I think you are overlooking the fact that the local var is the index of your foreach loop and thus, on each pass through the loop, will contain just one of the three variable names included in the local variables.

// original code
. local variables x1 x2 x3

. foreach var of varlist `variables' {
2. local nvars: word count `var'
3. local nrows=`nvars'+2
4. display
5. macro list _variables _var _nvars _nrows
6. }

_variables: x1 x2 x3
_var: x1
_nvars: 1
_nrows: 3

_variables: x1 x2 x3
_var: x2
_nvars: 1
_nrows: 3

_variables: x1 x2 x3
_var: x3
_nvars: 1
_nrows: 3

. // corrected code
. local variables x1 x2 x3

. foreach var of varlist `variables' {
2. local nvars: word count `variables'
3. local nrows=`nvars'+2
4. display
5. macro list _variables _var _nvars _nrows
6. }

_variables: x1 x2 x3
_var: x1
_nvars: 3
_nrows: 5

_variables: x1 x2 x3
_var: x2
_nvars: 3
_nrows: 5

_variables: x1 x2 x3
_var: x3
_nvars: 3
_nrows: 5

.

[William Lisowski]

I'm suspect post #9, which quotes post #7, was being written at the time I put post #8 up. I'm posting this to decrease the likelihood that post #8 gets accidentally overlooked.

But as long as I'm here, what I didn't point out in post #8 was that the while loop causes each variable to be written into all three rows. It was surgically removed from the code in post #8, but that change is easy to overlook.

[Jill Caviglia-Harris]

William:

Thanks. You are correct. I would have overlooked the removal of the while statement had you not noted this above. And, yes that was the issue. Thank you very much for helping with this!

I'm suspect post #9, which quotes post #7, was being written at the time I put post #8 up. I'm posting this to decrease the likelihood that post #8 gets accidentally overlooked.

But as long as I'm here, what I didn't point out in post #8 was that the while loop causes each variable to be written into all three rows. It was surgically removed from the code in post #8, but that change is easy to overlook.

[Shahida Pervin]

Hello. I wonder is there any command to export summary table to putdocx, like for regression "etable"? Thank you.

[Attaullah Shah]

asdoc can do it in one line.

ssc install asdoc
sysuse auto, clear
asdoc sum, label