oneway result to excel

[Gayeon Kim]

Hello. I need help you guys...


oneway MPU edu_dad [w=wgt], tab
: this is what I did for my oneway anova

but, I should add the result table(the picture) to excel.
but, I can't with outreg2 or putexcel codes...

Can you teach me how to put out the oneway result properly?

I hope I can add as much as everything from the analysis result.

Thank you

[Jeff Pitblado (StataCorp)]

Stata 19 provides new features that you can use to publish oneway
and tabulate, summarize() results to the document of your choice,
including Excel.

Here is an example, using the publicly available auto data.

sysuse auto

oneway mpg rep78 [w=weight], tab
* notice new r(ANOVA) matrix result; also Stata 19 now has a custom
* style for building ANOVA tables; see [TABLES] Example 8
return list

* -tabulate, summarize()- also reproduces the summary table reported by
* -oneway, tab-
tabulate rep78 [w=weight], summarize(mpg)

* let's collect the ANOVA results from -oneway-
collect create Oneway
collect : oneway mpg rep78 [w=weight], tab
collect style use anova, replace
collect layout

* Stata 19 adds built-in support for collections in -tabulate- with the
* new -collect- option
tabulate rep78 [w=weight], summarize(mpg) collect
collect layout

* list the collections in memory
collect dir

* combine the two collections
collect combine all = Tabulate Oneway, style(right)

* label your collections to produce table specific headers
collect label levels collection Tabulate "Summary of `:var label mpg'"
collect label levels collection Oneway "Analysis of variance"

* fix style to suit your preferences
collect style header rep78, title(label)

* update the layout, stacking/appending the row and column
* specifications, to produce both tables
collect layout ///
    (term rep78) ///
    (result[SS df MS F p mean sd frequency count]) ///
    (collection)

* publish to your document of choice
collect export oneway.xlsx, replace

Here are the resulting tables.

Summary of Mileage (mpg)
--------------------------------------------------------
                   |  Mean   Std. dev.   Frequency   Obs
-------------------+------------------------------------
Repair record 1978 |
               1   |  20.6         4.2       6,200     2
               2   |  18.7         3.7      26,830     8
               3   |  18.7         4.1      98,970    30
               4   |  20.3         4.9      51,660    18
               5   |  26.2         8.8      25,550    11
           Total   |  20.1         5.4     209,210    69
--------------------------------------------------------

Analysis of variance
-------------------------------------------------------------
               |       SS   df      MS   F statistic   Prob>F
---------------+---------------------------------------------
Between groups |   392.40    4   98.10          3.86   0.0072
 Within groups |  1626.60   64   25.42
---------------+---------------------------------------------
         Total |  2019.00   68   29.69
-------------------------------------------------------------

If you want to show Bartlett's test, you can construct a table-specific
note using collect note.

Here is a screenshot of Libreoffice from my Mac.

[Gayeon Kim]

Thank you very much! But, can I learn another one?.... Bcs my stata is 18.5 version... I am so sorry....

[Jeff Pitblado (StataCorp)]

If you have Stata 18.5 (which is actually StataNow 18), you should be able to freely upgrade to StataNow 19.

[Dave Airey]

It is nice to see how to do it with new features in Stata 19. You can also do this in v18.5 using just putexcel and table:

clear all
version 18.5

sysuse auto

putexcel set oneway_export.xlsx, replace

oneway mpg rep78 [fw=weight], tab
return list

putexcel A10=matrix(r(ANOVA)), names

tabulate rep78 [fw=weight], summarize(mpg)
return list

table rep78 [fw=weight], /// 
    stat(mean mpg) stat(sd mpg) stat(count mpg) nototal /// 
    export(oneway_export.xlsx, modify cell(A1))

[Jeff Pitblado (StataCorp)]

Actually the matrix result r(ANOVA) is not available in Stata 18 (or StataNow 18 -- i.e. Stata 18.5). It was added in Stata 19.

[Jeff Pitblado (StataCorp)]

Also table option export() is new in Stata 19, thus not availabel in Stata 18.

Here is David's example modified to work in Stata 18--and with the original weight specification.

sysuse auto

putexcel set oneway_export.xlsx, replace

oneway mpg rep78 [w=weight], tab
return list

collect get ///
    SS=(r(mss)) ///
    df=(r(df_m)) ///
    MS=(r(mss)/r(df_m)) ///
    F=(r(F)) ///
    p=(Ftail(r(df_m),r(df_r),r(F))) ///
    , tags(source["Between groups"])

collect get ///
    SS=(r(rss)) ///
    df=(r(df_r)) ///
    MS=(r(rss)/r(df_r)) ///
    , tags(source["Within groups"])

collect get ///
    SS=(r(mss)+r(rss)) ///
    df=(r(df_m)+r(df_r)) ///
    MS=((r(mss)+r(rss))/(r(df_m)+r(df_r))) ///
    , tags(source["Total"])

collect style cell, nformat(%18.2fc)
collect style cell result[df], nformat(%18.0fc)
collect style cell result[p], nformat(%6.4f) minimum(0.0001)
collect label levels result F "F statistic"
collect label levels result p "Prob>F"

collect layout (source) (result[SS df MS F p])

putexcel A10=collect

tabulate rep78 [w=weight], summarize(mpg)
return list

table rep78 [aw=weight], /// 
    stat(mean mpg) stat(sd mpg) stat(sumw) stat(count mpg)

collect style cell result[mean sd], nformat(%18.1f)
collect label levels result sd "Std. Dev.", modify
collect label levels result sumw "Frequency", modify
collect label levels result count "Obs", modify
collect preview

collect export oneway_export.xlsx, modify cell(A1)

Here is a screenshot of the exported tables in LibreOffice on Mac.