Is there a way to save 'esize output' for 'putexcel'?

[Anne Dam]

Dear stata forum,

I am trying to save output from estat esize (after anova), for automatic save in excel. However, I can't seem to make it work with esize.

I here use a very simple example data set:

use http://www.stata-press.com/data/r13/apple

putexcel set "apple trees", sheet("tree") modify
putexcel A1=("ES for apple trees") A2=("Eta sq") B2=("l_ci") C2=("u_ci")

    regress weight i.treatment
    estat esize

        scalar a2 = r(eta2)        
        scalar b2 = r(lb_eta2)
        scalar c2 = r(ub_eta2)

    putexcel A3=(a2) B3=(b2) C3=(c2)

The excel will save headers, but A3, B3 and C3 are empty.

Does anyone know what I am doing wrong?

Thanks

[William Lisowski]

The documentation for the use of estat esize after running regress is found in the output of help regress postestimation where - in my installation of Stata 17.0 - I see nothing that suggests r(eta2) etc. are returned by estat esize.

What follows demonstrates what I think you want, run on Stata 17.0.

use http://www.stata-press.com/data/r13/apple

putexcel set "apple trees", sheet("tree") modify
putexcel A1=("ES for apple trees") A2=("Eta sq") B2=("l_ci") C2=("u_ci")

    regress weight i.treatment
    estat esize

        scalar a2 = r(eta2)        
        scalar b2 = r(lb_eta2)
        scalar c2 = r(ub_eta2)

        scalar list a2 b2 c2
        
        matrix list r(esize)
        
        scalar a2 = r(esize)[1,1]
        scalar b2 = r(esize)[1,2]
        scalar c2 = r(esize)[1,3]
        
        scalar list a2 b2 c2 
        
    putexcel A3=(a2) B3=(b2) C3=(c2)

putexcel save

. use http://www.stata-press.com/data/r13/apple
(Apple trees)

. 
. putexcel set "apple trees", sheet("tree") modify

. putexcel A1=("ES for apple trees") A2=("Eta sq") B2=("l_ci") C2=("u_ci")
file apple trees.xlsx saved

. 
.     regress weight i.treatment

      Source |       SS           df       MS      Number of obs   =        10
-------------+----------------------------------   F(3, 6)         =     21.46
       Model |  5295.54433         3  1765.18144   Prob > F        =    0.0013
    Residual |  493.591667         6  82.2652778   R-squared       =    0.9147
-------------+----------------------------------   Adj R-squared   =    0.8721
       Total |    5789.136         9  643.237333   Root MSE        =      9.07

------------------------------------------------------------------------------
      weight | Coefficient  Std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
   treatment |
          2  |  -59.16667   7.405641    -7.99   0.000    -77.28762   -41.04572
          3  |     -33.25   8.279758    -4.02   0.007    -53.50984   -12.99016
          4  |      -34.4   8.279758    -4.15   0.006    -54.65984   -14.14016
             |
       _cons |      111.9   5.236579    21.37   0.000     99.08655    124.7134
------------------------------------------------------------------------------

.     estat esize

Effect sizes for linear models

-----------------------------------------------------------------
             Source | Eta-squared     df     [95% conf. interval]
--------------------+--------------------------------------------
              Model |   .9147383       3     .4496586    .9419436
                    |
          treatment |   .9147383       3     .4496586    .9419436
-----------------------------------------------------------------

. 
.         scalar a2 = r(eta2)        

.         scalar b2 = r(lb_eta2)

.         scalar c2 = r(ub_eta2)

. 
.         scalar list a2 b2 c2
        a2 =          .
        b2 =          .
        c2 =          .

.         
.         matrix list r(esize)

r(esize)[2,8]
                   eta2    lb_eta2    ub_eta2   epsilon2     omega2     df_num     df_den
       Model  .91473828  .44965857  .94194364  .87210743  .85988817          3          6
1b.treatment  .91473828  .44965857  .94194364  .87210743  .85988817          3          6

                      F
       Model  21.457187
1b.treatment  21.457187

.         
.         scalar a2 = r(esize)[1,1]

.         scalar b2 = r(esize)[1,2]

.         scalar c2 = r(esize)[1,3]

.         
.         scalar list a2 b2 c2 
        a2 =  .91473828
        b2 =  .44965857
        c2 =  .94194364

.         
.     putexcel A3=(a2) B3=(b2) C3=(c2)
file apple trees.xlsx saved

. 
. putexcel save

.

[Anne Dam]

Thank you so much William.

Yes that is excactly what I wanted!

Thank you for referring me to the correct location in the manual, I see I misread, however, I still have problems making it work in stata 14.0

I receive an error when I attempt to save r(esize)[1,1] as a scalar.

. putexcel set "apple trees", sheet("tree") modify

. putexcel A1=("ES for apple trees") A2=("Eta sq") B2=("l_ci") C2=("u_ci")
file apple trees.xlsx saved

.
.     regress weight i.treatment

      Source |       SS           df       MS      Number of obs   =        10
-------------+----------------------------------   F(3, 6)         =     21.46
       Model |  5295.54433         3  1765.18144   Prob > F        =    0.0013
    Residual |  493.591667         6  82.2652778   R-squared       =    0.9147
-------------+----------------------------------   Adj R-squared   =    0.8721
       Total |    5789.136         9  643.237333   Root MSE        =      9.07

------------------------------------------------------------------------------
      weight |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
-------------+----------------------------------------------------------------
   treatment |
          2  |  -59.16667   7.405641    -7.99   0.000    -77.28762   -41.04572
          3  |     -33.25   8.279758    -4.02   0.007    -53.50984   -12.99016
          4  |      -34.4   8.279758    -4.15   0.006    -54.65984   -14.14016
             |
       _cons |      111.9   5.236579    21.37   0.000     99.08655    124.7134
------------------------------------------------------------------------------

.     estat esize

Effect sizes for linear models

-------------------------------------------------------------------
             Source |   Eta-Squared     df     [95% Conf. Interval]
--------------------+----------------------------------------------
              Model |   .9147383         3     .4496586    .9419436
                    |
          treatment |   .9147383         3     .4496586    .9419436
-------------------------------------------------------------------

.         
.         matrix list r(esize)

r(esize)[2,9]
                   eta2    lb_eta2    ub_eta2     omega2  lb_omega2  ub_omega2     df_num     df_den
       Model  .91473828  .44965857  .94194364  .87210743  .17448785  .91291546          3          6
1b.treatment  .91473828  .44965857  .94194364  .87210743  .17448785  .91291546          3          6

                      F
       Model  21.457187
1b.treatment  21.457187

.         scalar a2 = r(esize)[1,1]
invalid syntax
r(198);

end of do-file

r(198);

.

(I have also tried scalar b2 = esize[1,2] , but without luck "esize not found" )

[Leonardo Guizzetti]

What version of Stata are you using?

[Anne Dam]

Hi Leonardo,

Stata/IC 14.2 for Mac

[William Lisowski]

In the future, please make clear, as the Statalist FAQ recommends, the version of Stata that you are using. The following code should work in Stata 14.

use http://www.stata-press.com/data/r13/apple

putexcel set "apple trees", sheet("tree") modify
putexcel A1=("ES for apple trees") A2=("Eta sq") B2=("l_ci") C2=("u_ci")

    regress weight i.treatment
    estat esize

        matrix r = r(esize)
        matrix list r
        
        scalar a2 = r[1,1]
        scalar b2 = r[1,2]
        scalar c2 = r[1,3]
        
        scalar list a2 b2 c2
        
    putexcel A3=(a2) B3=(b2) C3=(c2)

putexcel save

As will this simplification, I believe.

use http://www.stata-press.com/data/r13/apple

putexcel set "apple trees", sheet("tree") modify
putexcel A1=("ES for apple trees") A2=("Eta sq") B2=("l_ci") C2=("u_ci")

    regress weight i.treatment
    estat esize

        matrix r = r(esize)
        matrix list r
                
    putexcel A3=(r[1,1]) B3=(r[1,2]) C3=(r[1,3])

putexcel save

[Anne Dam]

Thank you both very much, this works! I will make sure to add my stata version everytime from now on. Thank you!

[William Lisowski]

Let me thank you as I should have earlier for the very clearly presented reproducible example that made it easy to see what the problem was, and to test and develop these solutions. That's not as common as we wish it were.

On Statalist we prefer to give solutions that work in the latest version of Stata when possible, because many Statalist followers use examples they see here to help learn the language (that's how I started), and why teach a new dog old tricks, to repurpose a familiar metaphor. I notice that in an earlier topic you posted your final code and graph for others to learn from, another thing we appreciate.