Export tabstat to excel

[Allan Andreotti]

Hi, i have been trying to export a tabstat result to excel on an specific sheet. I tried using putexcel command and sttab but none of them works fine. For example putexcel only export the total value. Here is an example of my database

Category 1	Category 2	Var 1	Var 2
1	1	5	7
1	2	7	10
2	2	2	9
3	1	3	4

So using a tabstat by category 1:

. tabstat var1 var2, stats(sum) by(category1) save

I got this tabstat

category1 var1 var2

1 12 17
2 2 9
3 3 4

Total 17 30

However when i export the result using putexcel it only export the Total values:

	var1	var2
Sum	17	30

[George Ford]

When you use by, Stata creates multiple matrices. You are only getting one of them. You'll need to putexcel all the matrixes [r(Stat1), r(Stat2), r(StatTotal)].

[Nick Cox]

Another way to think about this is that you can produce a new dataset and export it as a whole as data so that MS Excel can read it.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(category1 category2 var1 var2)
1 1 5  7
1 2 7 10
2 2 2  9
3 1 3  4
end

capture frame drop sandbox 

frame put category1 var1 var2, into(sandbox)

frame sandbox {
    collapse (sum) var1 var2, by(category1)
    export delimited * using wanted.csv 
}

type wanted.csv

I used export delimited here because I can check the results in Stata. Others might prefer to use export excel and check the results in Excel.

Note. This was cross-posted at https://www.reddit.com/r/stata/comme...to_excel_help/

Telling people about cross-posting elsewhere is a rule on Reddit r/stata and a request here. See FAQ Advice #8. This is (1) to avoid duplication of effort (2) to advertise that people interested in solutions may wish to look elsewhere.

[Allan Andreotti]

When you use by, Stata creates multiple matrices. You are only getting one of them. You'll need to putexcel all the matrixes [r(Stat1), r(Stat2), r(StatTotal)].

Thanks a lot for the help. Do you know a faster way to include all matrices at once? As some of my tabstat have over 100 rows.

[Jeff Pitblado (StataCorp)]

If you have Stata 17 or newer, you can use the reimagined table command to produce the summary statistics and collect export or putexcel collect to export your table to Excel.

Here is an example translating a simple tabstat command to table with some optional style changes and collect export to Excel.

sysuse auto

* -tabstat-
tabstat mpg turn trunk displ head, stats(sum) by(foreign)

* can be translated to -table-
table (foreign) (var), ///
        stat(total mpg turn trunk displ head)

* change some styles to match -tabstat- output
collect style header var, level(value)
collect style header foreign, title(name)
collect style cell result[total], nformat(%18.0g)
collect preview

collect export table.xlsx, replace

Here is the output from my Stata 17 session.

. sysuse auto
(1978 automobile data)

. 
. * -tabstat-
. tabstat mpg turn trunk displ head, stats(sum) by(foreign)

Summary statistics: Sum
Group variable: foreign (Car origin)

 foreign |       mpg      turn     trunk  displa~t  headroom
---------+--------------------------------------------------
Domestic |      1031      2155       767     12153       164
 Foreign |       545       779       251      2447      57.5
---------+--------------------------------------------------
   Total |      1576      2934      1018     14600     221.5
------------------------------------------------------------

. 
. * can be translated to -table-
. table (foreign) (var), ///
>         stat(total mpg turn trunk displ head)

-----------------------------------------------------------------------------------------------------------------
           |  Mileage (mpg)   Turn circle (ft.)   Trunk space (cu. ft.)   Displacement (cu. in.)   Headroom (in.)
-----------+-----------------------------------------------------------------------------------------------------
Car origin |                                                                                                     
  Domestic |          1,031               2,155                     767                   12,153              164
  Foreign  |            545                 779                     251                    2,447             57.5
  Total    |          1,576               2,934                   1,018                   14,600            221.5
-----------------------------------------------------------------------------------------------------------------

. 
. * change some styles to match -tabstat- output
. collect style header var, level(value)

. collect style header foreign, title(name)

. collect style cell result[total], nformat(%18.0g)

. collect preview

-----------------------------------------------------------
           |   mpg   turn   trunk   displacement   headroom
-----------+-----------------------------------------------
foreign    |                                               
  Domestic |  1031   2155     767          12153        164
  Foreign  |   545    779     251           2447       57.5
  Total    |  1576   2934    1018          14600      221.5
-----------------------------------------------------------

. 
. collect export table.xlsx, replace
(collection Table exported to file table.xlsx)

I have the Numbers app on my Mac instead of Excel, but here is a screenshot of the resulting spreadsheet.

[Allan Andreotti]

If you have Stata 17 or newer, you can use the reimagined table command to produce the summary statistics and collect export or putexcel collect to export your table to Excel.

Here is an example translating a simple tabstat command to table with some optional style changes and collect export to Excel.
[code]
sysuse auto

thanks a lot, that works really good