Help with collect combine

[Casey Slata]

I am having trouble with collect combine. I want to combine a table of means with a table of regression output using the collect command but it when I used collect combine it just returns my regression output. I suspect that it is because the column names differ in each table. Any advice is greatly appreciated.

. clear all

. webuse lbw, clear
(Hosmer & Lemeshow data)

.
. quiet recode age (min/20=1) (20/30=2) (31/max=3), gen(agegp)

.
. collect create tb1
(current collection is tb1)

. collect: regress low i.agegp i.race i.smoke

Source | SS df MS Number of obs = 189
-------------+---------------------------------- F(5, 183) = 3.33
Model | 3.38642568 5 .677285137 Prob > F = 0.0066
Residual | 37.1955849 183 .203254562 R-squared = 0.0834
-------------+---------------------------------- Adj R-squared = 0.0584
Total | 40.5820106 188 .215861758 Root MSE = .45084

------------------------------------------------------------------------------
low | Coefficient Std. err. t P>|t| [95% conf. interval]
-------------+----------------------------------------------------------------
agegp |
2 | .0398476 .0716078 0.56 0.579 -.1014355 .1811307
3 | -.1046758 .1171406 -0.89 0.373 -.3357955 .126444
|
race |
Black | .21478 .1016131 2.11 0.036 .0142962 .4152638
Other | .2019479 .0778359 2.59 0.010 .0483767 .3555191
|
smoke |
Smoker | .2219944 .072078 3.08 0.002 .0797836 .3642052
_cons | .1141078 .0808096 1.41 0.160 -.0453305 .2735462
------------------------------------------------------------------------------

. collect style row stack , nobinder spacer

. collect style column, dups(center)

. collect layout (colname[agegp]) (result[ _r_b _r_se _r_lb _r_ub ])

Collection: tb1
Rows: colname[agegp]
Columns: result[ _r_b _r_se _r_lb _r_ub ]
Table 1: 4 x 4

--------------------------------------------------------------------------------------
| Coefficient Std. error 95% lower bound 95% upper bound
------------------------------+-------------------------------------------------------
RECODE of age (Age of mother) |
1 | 0 0
2 | .0398476 .0716078 -.1014355 .1811307
3 | -.1046758 .1171406 -.3357955 .126444
--------------------------------------------------------------------------------------

.
. collect create tb2
(current collection is tb2)

. collect meanT=e(b): mean age, over(agegp)

Mean estimation Number of obs = 189

--------------------------------------------------------------
| Mean Std. err. [95% conf. interval]
-------------+------------------------------------------------
c.age@agegp |
1 | 18.07246 .207817 17.66251 18.48242
2 | 24.78 .2765516 24.23446 25.32554
3 | 33.35 .7155234 31.93851 34.76149
--------------------------------------------------------------

. collect meanN=e(_N): mean age, over(agegp)

Mean estimation Number of obs = 189

--------------------------------------------------------------
| Mean Std. err. [95% conf. interval]
-------------+------------------------------------------------
c.age@agegp |
1 | 18.07246 .207817 17.66251 18.48242
2 | 24.78 .2765516 24.23446 25.32554
3 | 33.35 .7155234 31.93851 34.76149
--------------------------------------------------------------

. collect layout (colname) (result [meanN meanT])

Collection: tb2
Rows: colname
Columns: result [meanN meanT]
Table 1: 3 x 2

----------------------------------------------------------------
| meanN meanT
------------------------------------------------+---------------
Age of mother @ RECODE of age (Age of mother)=1 | 69 18.07246
Age of mother @ RECODE of age (Age of mother)=2 | 100 24.78
Age of mother @ RECODE of age (Age of mother)=3 | 20 33.35
----------------------------------------------------------------

.
. collect combine final = tb1 tb2, layout(left) style(left) label(left) warn
Note: collect is ignoring label "Sample DF" for level df_r of dimension result.
(current collection is final)

.
. collect export "table1.xlsx"
(collection final exported to file table1.xlsx)

*this is the table that collect combine generates:
--------------------------------------------------------------------------------------
| Coefficient Std. error 95% lower bound 95% upper bound
------------------------------+-------------------------------------------------------
RECODE of age (Age of mother) |
1 | 0 0
2 | .0398476 .0716078 -.1014355 .1811307
3 | -.1046758 .1171406 -.3357955 .126444
--------------------------------------------------------------------------------------

[Jeff Pitblado (StataCorp)]

Thank you for providing your example code using publicly available data.

I'm going to assume you want the mean results to show up to the right of your regress results, and the table rows are to be composed from the levels of the recoded age groups in variable agegp.

webuse lbw, clear

quiet recode age (min/20=1) (20/30=2) (31/max=3), gen(agegp)
* add labels to the new variable coding age groups
label define agegp 1 "<= 20" 2 "21-30" 3 "> 30"
label values agegp agegp
label variable agegp "Age group"

collect create tb1
collect: regress low i.agegp i.race i.smoke
collect style row stack , nobinder spacer
collect style column, dups(center)
* factor variables in the regression, like -i.agegp-, are also present
* as dimensions in the collection, we can use them in the layout
collect layout (agegp) (result[ _r_b _r_se _r_lb _r_ub ])

collect create tb2
* call -mean- once
mean age, over(agegp)
* collect your custom results afterward using -collect get- to prevent
* collection of more results than you want; bind the expressions in parens
* to prevent -collect- from also collecting other -e()- results
collect get meanT=(e(b)) meanN=(e(_N))
collect layout (agegp) (result)

* these two collections are different enough that we will need to
* redefine the layout, so no need to keep either of the previous layouts
collect combine final = tb1 tb2, style(left) label(left) warn

* our new layout for the combined results
collect layout (agegp) (result[_r_b _r_se _r_ci meanT meanN])
* refine some labels
collect label levels result meanT "Mean" meanN "N", modify
* show the label for dimension -agegp-, it gives context to its level labels
collect style header agegp, title(label)
collect preview

Here is the resulting table.

-------------------------------------------------------------------
          | Coefficient Std. error        95% CI           Mean   N
----------+--------------------------------------------------------
Age group |
  <= 20   |           0          0                     18.07246  69
  21-30   |    .0398476   .0716078 -.1014355  .1811307    24.78 100
  > 30    |   -.1046758   .1171406 -.3357955   .126444    33.35  20
-------------------------------------------------------------------