Using collect command to display results by quartile

[Chirag Yadav]

I want to make a table that shows regression results for a bunch of dependent variables against the same independent variable by quartile groups something like this:

	Q1	Q2	Q3	Q4
Dep var 1
Dep var 2
Dep var 3
Mean of sample

This is my example code:

sysuse auto, clear
xtile price_quart = price, n(4)

collect clear 

foreach var of varlist rep78 headroom trunk weight{
    bys price_quart: collect: reg `var' mpg
}

collect layout (colname#cmdset#result[_r_b _r_se]) (price_quart)

This makes the values show up in individual row:

How can I get the same dependent variable values to be displayed in the same row?
And is there a way to display the mean of price variable at the bottom by quartile?

[Jeff Pitblado (StataCorp)]

Thanks for the working example.

A small change to your layout will fix the row placement of the coefficients and standard errors. I also added some code to show the means of the independent variable within the quartiles.

sysuse auto, clear
xtile price_quart = price, n(4)

collect clear 

foreach var of varlist rep78 headroom trunk weight{
    bys price_quart: collect: reg `var' mpg
}

collect layout (colname#cmdset#result[_r_b _r_se]) (price_quart)

* change -cmdset- to -coleq-
collect layout (colname#coleq#result[_r_b _r_se]) (price_quart)
collect preview

* add means of indepvar
bys price_quart: collect, tag(Mean[_hide]): sum mpg
* add separator
collect style cell Mean, border(top)
collect layout (colname#coleq#result[_r_b _r_se] Mean#result[mean]) (price_quart)

Here is the resulting table.

-----------------------------------------------------------------
                        |         1         2         3         4
------------------------+----------------------------------------
Mileage (mpg)           |                                        
  Repair record 1978    |                                        
    Coefficient         |  .1421708  .1202839  .0575758  .0863231
    Std. error          |  .0418936  .0303852  .0382828  .0440138
  Headroom (in.)        |                                        
    Coefficient         | -.0869037 -.0795455 -.0409091  -.102846
    Std. error          |   .036981  .0363444   .034722  .0292255
  Trunk space (cu. ft.) |                                        
    Coefficient         | -.3590783 -.3768939 -.3060606 -.5383225
    Std. error          |  .1198979  .1610064  .1685651  .1622297
  Weight (lbs.)         |                                        
    Coefficient         |  -95.2698 -100.3472 -85.78788 -143.8563
    Std. error          |  12.86579  20.13561  19.07369   29.6229
Intercept               |                                        
  Repair record 1978    |                                        
    Coefficient         | -.2930886  .5558461  2.427432   2.03566
    Std. error          |  1.024801  .7322057  .7982089  .8005348
  Headroom (in.)        |                                        
    Coefficient         |  5.071966  4.856061  3.739234  4.901069
    Std. error          |  .9000655  .8705813  .7239658  .5408686
  Trunk space (cu. ft.) |                                        
    Coefficient         |  19.82434  21.79419  21.54226  25.04879
    Std. error          |   2.91815  3.856693  3.514641  3.002346
  Weight (lbs.)         |                                        
    Coefficient         |  4967.222  5083.657    4842.6  6106.422
    Std. error          |  313.1355  482.3215  397.6931  548.2237
------------------------+----------------------------------------
Mean                    |  23.84211  23.33333        20  17.94444
-----------------------------------------------------------------

If you do not want to see the intercepts, then

. * specify the indepvar in -colname- to suppress the intercepts
. collect layout (colname[mpg]#coleq#result[_r_b _r_se] Mean#result[mean]) (price_quart)

Collection: default
      Rows: colname[mpg]#coleq#result[_r_b _r_se] Mean#result[mean]
   Columns: price_quart
   Table 1: 14 x 4

-----------------------------------------------------------------
                        |         1         2         3         4
------------------------+----------------------------------------
Mileage (mpg)           |                                        
  Repair record 1978    |                                        
    Coefficient         |  .1421708  .1202839  .0575758  .0863231
    Std. error          |  .0418936  .0303852  .0382828  .0440138
  Headroom (in.)        |                                        
    Coefficient         | -.0869037 -.0795455 -.0409091  -.102846
    Std. error          |   .036981  .0363444   .034722  .0292255
  Trunk space (cu. ft.) |                                        
    Coefficient         | -.3590783 -.3768939 -.3060606 -.5383225
    Std. error          |  .1198979  .1610064  .1685651  .1622297
  Weight (lbs.)         |                                        
    Coefficient         |  -95.2698 -100.3472 -85.78788 -143.8563
    Std. error          |  12.86579  20.13561  19.07369   29.6229
------------------------+----------------------------------------
Mean                    |  23.84211  23.33333        20  17.94444
-----------------------------------------------------------------

[Chirag Yadav]

Thank you! That's exactly what I needed.
What exactly does coleq do? I understand cmdset from here, but this does not explain what coleq does? Thanks again.

[Jeff Pitblado (StataCorp)]

coleq is taken from the column equations of the matrix e(b) -- just like colname is taken from its column names.

This and many other details about dimensions attached to collected results are mentioned in [TABLES] Intro 2.

[Chirag Yadav]

Hi,

If I directly wanted correlation coefficients instead of using the regression coefficients could I use the same set of commands but use corr instead of reg? I tried this and a few variations of this but fail to get the coefficients:

sysuse auto, clear
xtile price_quart = price, n(4)
collect clear 

foreach var of varlist rep78 headroom trunk weight{
    bys price_quart: collect: corr `var' mpg
}

bys price_quart: collect, tag(Mean[_hide]): sum mpg

collect style cell Mean, border(top)

collect layout (colname#coleq#result[_rho] Mean#result[mean]) (price_quart)

The output comes out without any correlation coefficients displayed:

------------------------------------
     |        1        2  3        4
-----+------------------------------
Mean | 23.84211 23.33333 20 17.94444
------------------------------------

[Sarita Vaswani]

It might be easier to make correlation tables with estpost and esttab

[Chirag Yadav]

I have tried that but estpost correlate does not seem to be working with by()

[Jeff Pitblado (StataCorp)]

In your correlations example, you are collecting scalar r(rho) from command correlate. In your layout you specify result[_rho], but it should be result[rho]. Additionally, scalars are not like matrices, they do not have column names or equations, but you can tag the scalars using colname and coleq with option tags() in your collect: prefix to correlate.

In the following I highlight my changes to your code in blue.

cls
clear all

sysuse auto, clear
xtile price_quart = price, n(4)
collect clear

foreach var of varlist rep78 headroom trunk weight{
    bys price_quart: collect, tags(coleq[`var'] colname[mpg]): corr `var' mpg
}

bys price_quart: collect, tag(Mean[_hide]): sum mpg

collect style cell Mean, border(top)

* change -result[_rho]- to -result[rho]-; remove the underscore
collect layout (colname#coleq#result[rho] Mean#result[mean]) (price_quart)

Here is the resulting table.

-----------------------------------------------------------------
                        |         1         2         3         4
------------------------+----------------------------------------
Mileage (mpg)           |                                        
  Repair record 1978    |                                        
    ρ                   |  .6469415  .7267432  .3426785  .4642589
  Headroom (in.)        |                                        
    ρ                   | -.4951691 -.4800078 -.2747554 -.6605277
  Trunk space (cu. ft.) |                                        
    ρ                   | -.5876895 -.5050826 -.4030204 -.6384727
  Weight (lbs.)         |                                        
    ρ                   | -.8736923 -.7798644 -.7371372 -.7718732
------------------------+----------------------------------------
Mean                    |  23.84211  23.33333        20  17.94444
-----------------------------------------------------------------