Scaling cell values in collect layout

[Matt Williams]

Dear Statalist,

I have designed a table to my specifications displaying the resuts _r_b in cells. This reflects a rate that I would like to scale by multiplying by 1000. I can easily export to excel and finish the table there, but it would be nice to do this within stata. It is not an option to rerun the analysis with a scaled variable as the code takes a long time to run (not to mention I would hope such a simple formatting desire is accomplishable post-analysis). I am looking for something like collect style results _r_b*1000.

I'm not sure if a reproducible example is necessary, but to comply here is code that will generate a table (this time the result is "mean" not _r_b). I would like to multiple the mean in the table by 1000 using collection manipulations rather than transforming the variable before analysis.

webuse auto, clear
gen length_1000 = length/1000
collect: table foreign, stat(mean length_1000) name(mytable)

I could not find an answer searching prior posts. I apologize if this has been discussed before.

Thank you in advance.

[Andrew Musau]

Dear Statalist,

I have designed a table to my specifications displaying the resuts _r_b in cells. This reflects a rate that I would like to scale by multiplying by 1000.

I am looking for something like collect style results _r_b*1000.

As far as I know, there is no such option in collect. You need to include the transformation at the point that you are running the estimation. Something like:

sysuse auto, clear
collect clear
collect Scaled=(r(table)["b",1...]*1000): regress mpg disp weight turn
collect layout (colname) (result[_r_b Scaled])

Res.:

. collect Scaled=(r(table)["b",1...]*1000): regress mpg disp weight turn

      Source |       SS           df       MS      Number of obs   =        74
-------------+----------------------------------   F(3, 70)        =     44.47
       Model |  1602.59024         3  534.196747   Prob > F        =    0.0000
    Residual |  840.869218        70  12.0124174   R-squared       =    0.6559
-------------+----------------------------------   Adj R-squared   =    0.6411
       Total |  2443.45946        73  33.4720474   Root MSE        =    3.4659

------------------------------------------------------------------------------
         mpg | Coefficient  Std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
displacement |   .0055957    .009906     0.56   0.574    -.0141613    .0253527
      weight |  -.0059274   .0014326    -4.14   0.000    -.0087847   -.0030701
        turn |  -.1386576   .1793412    -0.77   0.442    -.4963424    .2190272
       _cons |   43.58846   4.964215     8.78   0.000     33.68765    53.48928
------------------------------------------------------------------------------

. 
. collect layout (colname) (result[_r_b Scaled])

Collection: default
      Rows: colname
   Columns: result[_r_b Scaled]
   Table 1: 4 x 2

----------------------------------------------
                       | Coefficient    Scaled
-----------------------+----------------------
Displacement (cu. in.) |    .0055957  5.595699
Weight (lbs.)          |   -.0059274 -5.927417
Turn circle (ft.)      |   -.1386576 -138.6576
Intercept              |    43.58846  43588.46
----------------------------------------------

.

It is not an option to rerun the analysis with a scaled variable as the code takes a long time to run (not to mention I would hope such a simple formatting desire is accomplishable post-analysis).

As you have the estimation results stored, you can just create a matrix with the rescaled coefficients, thus avoid rerunning the analysis.

sysuse auto, clear
collect clear
collect: regress mpg disp weight turn
collect get myb = e(b)*1000
collect layout (colname) (result[_r_b myb])

Res.:

. collect layout (colname) (result[_r_b myb])

Collection: default
      Rows: colname
   Columns: result[_r_b myb]
   Table 1: 4 x 2

----------------------------------------------
                       | Coefficient       myb
-----------------------+----------------------
Displacement (cu. in.) |    .0055957  5.595699
Weight (lbs.)          |   -.0059274 -5.927417
Turn circle (ft.)      |   -.1386576 -138.6576
Intercept              |    43.58846  43588.46
----------------------------------------------

[Matt Williams]

Thanks so much, this is helpful.

Unfortunately at this stage, I only have the collections stored as jtson files that I then load for manipulation. The regression call is long gone (happened during a previous instance of Stata). are there any options in this case?

Sorry for not making that clearly from the start.

[Andrew Musau]

You could reconstruct the matrices, e.g., by exporting the results to Excel and importing, but it may not be worth the effort. More likely, there is some way to retrieve them from the stored collections. Here is an example, assuming that I have the collections stored.

webuse nhanes2l, clear
collect clear
collect create mycollection
collect _r_b _r_se: regress bpsystol age weight i.region
collect layout (colname) (result)
collect style showbase off
collect style cell, nformat(%6.5f)
collect preview

collect export "my_results.xlsx", replace
clear
import excel "my_results.xlsx", firstrow clear
ds A, not
mkmat `r(varlist)', mat(res)
collect dir

*GET MATRIX ROWNAMES FROM COLLECTION
qui collect levelsof colname
display "`s(levels)'"

*RENAME MATRIX WITH VARNAMES
mat rownames res= age weight 2.region 3.region 4.region _cons

*RESCALE MATRIX COLUMNS
collect get Coefficient= res[1..., 1]'*1000, name(mycollection)
collect get SE= res[1..., 2]'*1000, name(mycollection)

*OUTPUT TRANSFORMED RESULTS
collect layout (colname) (result[Coefficient SE])
collect style cell, nformat(%5.2f)
collect preview

Res.:

. collect _r_b _r_se: regress bpsystol age weight i.region

      Source |       SS           df       MS      Number of obs   =    10,351
-------------+----------------------------------   F(5, 10345)     =    900.55
       Model |  1708779.02         5  341755.804   Prob > F        =    0.0000
    Residual |     3925891    10,345  379.496472   R-squared       =    0.3033
-------------+----------------------------------   Adj R-squared   =    0.3029
       Total |  5634670.03    10,350  544.412563   Root MSE        =    19.481

------------------------------------------------------------------------------
    bpsystol | Coefficient  Std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
         age |   .6383029   .0111397    57.30   0.000     .6164668    .6601389
      weight |   .4069294   .0124796    32.61   0.000      .382467    .4313917
             |
      region |
         MW  |  -.2397311   .5640029    -0.43   0.671    -1.345286    .8658237
          S  |  -.6187414   .5604584    -1.10   0.270    -1.717348    .4798654
          W  |  -.8617777    .570496    -1.51   0.131     -1.98006    .2565047
             |
       _cons |   71.70779   1.107732    64.73   0.000     69.53642    73.87916
------------------------------------------------------------------------------


. collect preview

------------------------------------
            | Coefficient Std. error
------------+-----------------------
Age (years) |     0.63830    0.01114
Weight (kg) |     0.40693    0.01248
MW          |    -0.23973    0.56400
S           |    -0.61874    0.56046
W           |    -0.86178    0.57050
Intercept   |    71.70779    1.10773
------------------------------------


.

.
. *OUTPUT TRANSFORMED RESULTS


. collect preview

---------------------------------
            | Coefficient      SE
------------+--------------------
Age (years) |      638.30   11.14
Weight (kg) |      406.93   12.48
MW          |     -239.73  564.00
S           |     -618.74  560.46
W           |     -861.78  570.50
Intercept   |    71707.79 1107.73
---------------------------------

.

[Matt Williams]

Thank you for this creative solution.

I think I will opt for finishing these particular tables in excel, given I have to rely on it anyway as a intermediate step here (albeit programmatically!).

If anyone else knows how to retrieve these from the stored collection directly and/or manipulate those results, I would be grateful for the future.