Export output from table command

[Hendri Adriaens]

Hello,

I would like to know how to export output from the table command to Excel.

Thank you, best regards,
-Hendri.

[Maarten Buis]

One possibility is using tabout, see: ssc descr tabout

[Hendri Adriaens]

Thank you for the suggestion, but tab(out) does something different than table. And it doesn't export to Excel.

Maybe I have to rephrase the question. I want to compute the sum of variable A, by variable B (rows) and variable C (columns). Similar to a pivottable in Excel. This is possible with the table command. I want to export the result to Excel.

How to do this? Or is there any other command that gives me pivottable functionality and allows for exports to Excel?

Thank you.

[Charlie Joyez]

Actually tabout does allow you to do some cross-tabulation and display the sum.
But the syntax is quite complex to obtain the precise display you want.
You'd better check the following tutorial (p4-5 espacially): http://www.ianwatson.com.au/stata/tabout_tutorial.pdf

[Nick Cox]

You could collapse to a reduced dataset, and then export.

[Hendri Adriaens]

Hi Charlie, Nick,

thanks for the suggestions. Collapse allows for this functionality indeed, but the output will not be in the format that I would like (like the output of table). I requires at least another reshape and some editing in Excel. I will have a look whether I can get quick results with tabout.

More in general: it's unfortunate that each command has unique abilities and that there is no globally working method to export results from any command. The first trick is to find out where results are stored. Unfortunately, table does not store any results in an accessible way.

Best regards,
-Hendri.

[Nick Cox]

I don't know anything much about Excel, which I gather is a spreadsheet program from Microsoft.

But I don't think this is very difficult. Even with the messiness of missing values there are easy work-arounds. What's different about your example(s)? As you give neither examples of data nor specific code that you tried, more precise advice would depend on further details.

 

. sysuse auto, clear 
(1978 Automobile Data)

. collapse (sum) weight, by(foreign rep78)

. replace rep78 = 6 if missing(rep78) 
(2 real changes made)

. label def rep78 6 missing 

. label val rep78 rep78 

. reshape wide weight, i(foreign) j(rep78)
(note: j = 1 2 3 4 5 6)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                       10   ->       2
Number of variables                   3   ->       7
j variable (6 values)             rep78   ->   (dropped)
xij variables:
                                 weight   ->   weight1 weight2 ... weight6
-----------------------------------------------------------------------------

. list 

     +----------------------------------------------------------------------+
     |  foreign   weight1   weight2   weight3   weight4   weight5   weight6 |
     |----------------------------------------------------------------------|
  1. | Domestic     6,200    26,830    92,940    31,790     3,920    10,810 |
  2. |  Foreign         .         .     6,030    19,870    21,630     3,420 |
     +----------------------------------------------------------------------+

[Hendri Adriaens]

Hi Nick,

thanks for the reply. Indeed, collapse + reshape do the trick, although the output from table is visually a bit more pleasing, but it's impossible to export that. I've settled for your solution.

Best regards,
-Hendri.

[Doug Hemken]

You should also investigate the Stata command "putexcel", and look up "Stata Automation". I have not personally used either, but if you have hundreds of tables to output, this might be useful.