Using collect and margins to create custom table

[Colin Hubbard]

I would like to use -margins, pwcompare- to generate a table. Here is an example using the auto data set:

sysuse auto
reg mpg i.foreign
collect: margins foreign, pwcompare(eff)
collect label levels result _r_b "MPG", modify
collect label levels result b_vs "MPG", modify
collect layout (colname[0.foreign 1.foreign]) (result[_r_b _r_ci])
collect layout (1vs0.foreign) (result[b_vs])

This gives:

--------------------------------------
         |      MPG       95% CI      
---------+----------------------------
Domestic | 19.82692 18.34634  21.30751
Foreign  | 24.77273 22.49646  27.04899
--------------------------------------

and

--------
     MPG
--------
4.945804
--------

But, what I would like is something like this:

-------------------------------------------------
         | MPG              95% CI       p-value
---------+---------------------------------------
Domestic | 19.82692 18.34634  21.30751
Foreign  | 24.77273 22.49646  27.04899
-------------------------------------------------
Diff     | 4.945804 2.230384   7.66123    0.001
-------------------------------------------------

I cannot figure out how to extract the confidence interval and the p-value for the pairwise comparison, nor how to stack the tables as shown. Any help would be greatly appreciated.

Thanks!

[Andrew Musau]

Thanks for the reproducible example. Here is one approach - follow it to add the p-value (_r_p).

sysuse auto, clear
collect clear
reg mpg i.foreign
collect: margins foreign, pwcompare(eff)
collect get lb_vs= r(table_vs)["ll", 1], tags(colname[1vs0.foreign])
collect get ub_vs= r(table_vs)["ul", 1], tags(colname[1vs0.foreign])
collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub]
collect layout (colname[0.foreign 1.foreign 1vs0.foreign]) (result[_r_b _r_lb _r_ub])

Res.:

. collect layout (colname[0.foreign 1.foreign 1vs0.foreign]) (result[_r_b _r_lb _r_ub])

Collection: default
      Rows: colname[0.foreign 1.foreign 1vs0.foreign]
   Columns: result[_r_b _r_lb _r_ub]
   Table 1: 3 x 3

-----------------------------------------------------------------
                    | Coefficient 95% lower bound 95% upper bound
--------------------+--------------------------------------------
Domestic            |    19.82692        18.34634        21.30751
Foreign             |    24.77273        22.49646        27.04899
Foreign vs Domestic |    4.945804        2.230384        7.661225
-----------------------------------------------------------------

[Andrew Musau]

By the way, if you wanted to have the lower-bound and upper-bound as one statistic, e.g., "_r_ci" in #1, you'd define a composite result. See

help collect composite

sysuse auto, clear
collect clear
reg mpg i.foreign
collect: margins foreign, pwcompare(eff)
collect get lb_vs= r(table_vs)["ll", 1], tags(colname[1vs0.foreign])
collect get ub_vs= r(table_vs)["ul", 1], tags(colname[1vs0.foreign])
collect get pvalue= r(table_vs)["pvalue", 1], tags(colname[1vs0.foreign])
collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub]
collect composite define ci = _r_lb _r_ub, delimiter(" ")
collect label levels result ci "95% Conf. Int."
collect layout (colname[0.foreign 1.foreign 1vs0.foreign]) (result[_r_b ci pvalue])
collect style cell colname[1.foreign], border(bottom)
collect label levels colname 1vs0.foreign "Difference"
collect style cell, nformat(%4.3f)
collect preview

Res.:

. collect preview

----------------------------------------------
           | Coefficient 95% Conf. Int. pvalue
-----------+----------------------------------
Domestic   |      19.827  18.346 21.308       
Foreign    |      24.773  22.496 27.049       
-----------+----------------------------------
Difference |       4.946    2.230 7.661  0.001
----------------------------------------------

.

[Colin Hubbard]

Thank you so much, Andrew! That is exactly what I was looking for!