Exporting results from a two-way anova

[ghoetker]

I want to export results of a two-way anova to an Excel sheet. I'd be happy to export essentially what appears in the "Results" pane, but at least want to export SS df F Prob>F and R2. The following code gets me close

collect clear
collect: anova max_performance_at_step exp_level opt_level exp_level#opt_level

collect label levels term 1.exp_level "exp_level", modify

collect style cell result[p], nformat(%9.4f)
collect label levels term 1.opt_level "opt_level", modify  
collect label levels term 1.exp_level#1.opt_level "exp_level#opt_level", modify

collect layout (term[Model 1.exp_level 1.opt_level 1.exp_level#1.opt_level Residual]) (result[SS df ms F p r2])

--------------------------------------------------------
                    |       SS     df F statistic Prob>F
--------------------+-----------------------------------
Model               | 43.27497      8    199.7384 0.0000
exp_level           | 40.05307      2    739.4701 0.0000
opt_level           | .1526113      2     2.81755 0.0598
exp_level#opt_level | 3.069288      4    28.33299 0.0000
Residual            | 3436.497 126891                   
--------------------------------------------------------

but I can't figure out how to include the r2.

Very open to approaches that don't involve collect.

Thank you for any help.

[Rich Goldstein]

R2 is returned in e(r2) - see "ereturn li" for more; so you could put that into a "collect get" statement

[Hemanshu Kumar]

Consider:

sysuse nlsw88, clear

collect clear
collect: anova wage race occupation race#occupation

collect label levels term 1.race "race", modify

collect style cell result[p], nformat(%9.4f)
collect label levels term 1.occupation "occupation", modify  
collect label levels term 1.race#1.occupation "race#occupation", modify

collect addtags term[R2], fortags(result[r2])
collect recode result r2 = SS
collect layout (term[Model 1.race 1.occupation 1.race#1.occupation Residual R2]) (result[SS df ms F p])

which produces:

. collect preview

--------------------------------------------------
                |       SS   df F statistic Prob>F
----------------+---------------------------------
Model           | 10436.56   28    12.90297 0.0000
race            | 3.762987    2    .0651318 0.9369
occupation      | 2544.815   12    7.341168 0.0000
race#occupation |  209.054   14    .5169168 0.9249
Residual        | 63783.58 2208                  
R2              | .1406163                        
--------------------------------------------------

[ghoetker]

Thank you for the fantastic solution. It works perfectly.

If (and only if) you had a moment, can you explain the role of collect addtags term[R2], fortags(result[r2])? Despite having read the manual several times, I'm unclear about what it does and why we need it.

Any additional enlightenment much appreciated. Regardless, thank you you for sharing the solution.

[Hemanshu Kumar]

It becomes easy to fill out a table if you can get each value you want in it to be uniquely identified by two tags -- one representing the row you want it in, the other the column. Since your collect layout was using levels of the dimension term to specify the individual rows, I just made sure that the value you wanted was also tagged with a level of term, i.e. R2. And then I also decided that I would want the column to the same one as the sums of squares, and so I recoded result[r2] to result[SS] as well.

[ghoetker]

Thank you for the explanation. "It becomes easy to fill out a table if you can get each value you want in it to be uniquely identified by two tags -- one representing the row you want it in, the other the column." made a lot of the collect approach click with me at last. Really appreciate the extra effort on your part.