Exporting results of the -table- command to Excel

[Khai Do Ba]

I'm currently trying to export results of the table command to Excel, to no avail.

I am following the guide from http://www.ats.ucla.edu/stat/stata/faq/tab_nway.htm since I need 3-way (or more) cross-tabulation of medians of another variable (not just frequency).

So for example my code is as follows:

table region_alt fav_platform fav_maincat_rev, contents(median orders_per_yr) format(%4.2f)

with results:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
          |                                                              fav_maincat_rev and fav_platform                                                              
region_al | ------------------ Book_fav_rev -----------------    ------------------- LS_fav_rev ------------------    ------------------- EL_fav_rev ------------------
t         |         desktop           mobile  nopref_platform            desktop           mobile  nopref_platform            desktop           mobile  nopref_platform
----------+------------------------------------------------------------------------------------------------------------------------------------------------------------
     HCMC |            4.53             6.02             2.99               5.53             6.52             3.99               4.56             5.45             3.48
       HN |            5.00             6.19             3.74               5.84             6.64             3.68               4.61             5.62             3.72
    Other |            5.38             6.64             3.67               6.26             7.02             4.29               4.87             5.98             3.63
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Things I have tried:

• Stata's "Copy table" feature doesn't work, and merges random columns together
• The tab function has options for "matcell()" and "matrow()" that allows using putexcel (e.g. http://blog.stata.com/2013/09/25/exp...bles-to-excel/), but table doesn't have this
• The table function does not save anything to a matrix to easily export; e.g. using return list only yields a single scalar r(N)
• I couldn't get outreg2 to do n-way cross-tab like this

I am on Stata/MP 14.1. This is my first post so I'm not sure about posting etiquette - please let me know if I need to fix anything, if any more information is needed, etc.

Thanks in advance for any help!

[William Lisowski]

Welcome to Statalist!

The following may start you on your way to a solution.

The output of help table suggests that the replace option will replace in memory the data from which the table is built (which you have previously saved or can easily reproduce) with the table statistics. You can then use export excel to write that into an Excel workbook.

[Khai Do Ba]

Thank you for your reply! This method certainly works, although it seems a bit inefficient - since this option completely replaces the dataset being used, to be able to multiple tables, I would need to clear and re-open the source data multiple times, a process that becomes slow and unwieldy with large datasets and multiple exports. I wonder if there is a better way to do this that doesn't clear out the source data?

[William Lisowski]

You may be surprised to learn that many of Stata's commands are implemented as programs that do just that - save a temporary copy of the data as it exists at the start of the program and use the copy at the end of the program to restore the data to its state when the user issued the command. My general approach in the circumstances you describe is

use master_data
// prepare data for tabulations
....
// if the dataset is particularly large, drop observations and variables not needed for the tabulation
drop var32 var92 ...
drop if var85!=1
...
// save a temporary copy of the prepared data
tempfile tab_input
save `tab_input'
// do a tabulation and export the results
...
// return the prepared data to memory
use `tab_input', clear
// do another tabluation ..
...

i've found this neither slow nor unwieldy.

[Jorrit Gosens]

The preserve and restore commands could be another alternative.
http://www.stata.com/manuals13/ppreserve.pdf

[William Lisowski]

In circumstances like this, where the identical file is needed repeatedly, using preserve and restore requires that after each restore, a subsequent preserve is needed to allow for the next restore. So while I am a fan of preserve and restore, for blocks of code like this, I've moved to using the tempfile approach for the (minor, in my case) gains in efficiency.

[Clyde Schechter]

Well, actually, you don't have to explicitly create a tempfile to reap this time savings. The -restore- command has a -preserve- option that restores the file, but keeps the -preserve-d version around for future use without re-writing it. The code below compares the three strategies.

. clear*

. 
. sysuse auto
(1978 Automobile Data)

. 
. //      USING restore, preserve
. timer on 1

. preserve

. forvalues i = 1/10000 {
  2.         quietly keep if mpg < 20
  3.         restore, preserve
  4. }

. timer off 1

. 
. restore

. 
. //      USING REPEATED preserve AND restores
. timer on 2

. forvalues i = 1/10000 {
  2.         preserve
  3.         quietly keep if mpg < 20
  4.         restore
  5. }

. timer off 2

. 
. // USING AN EXPLICIT TEMPFILE
. tempfile holding

. save `holding'
file C:\Users\CLYDES~1\AppData\Local\Temp\ST_0d0008m6.tmp saved

. timer on 3

. forvalues i = 1/10000 {
  2.         quietly keep if mpg < 20
  3.         quietly use `holding', clear
  4. }

. timer off 3

. 
. timer list
   1:      0.94 /        1 =       0.9400
   2:     16.91 /        1 =      16.9110
   3:      0.92 /        1 =       0.9200

Clearly repeatedly using restore and preserve is monstrously inefficient compared to either of the other two approaches. -tempfile- and -restore, preserve- are about equally speedy. (I've actually run this several times, and it seems that each of them comes out slightly ahead of the other about half the time.) I think the advantage of using -restore, preserve- here is that there is just one less local macro floating around that has to be referred to--not really a compelling case either way.

One other difference between the first and third approaches. Once the do-file reaches its end, using the -restore, preserve- approach leaves a copy of the original data waiting to be restored, and it will be restored automatically at the end of the do-file (unless you do something to prevent that from happening). Using the tempfile approach will not result in any automatic change to the data at the end of the do-file, so whatever happens to be in memory (which might or might not be the original data depending on what happens after the loop) at that time will stay there unless you explicitly -use `holding'- again at the end.

[William Lisowski]

Using the tempfile approach will not result in any automatic change to the data at the end of the do-file, so whatever happens to be in memory (which might or might not be the original data depending on what happens after the loop) at that time will stay there unless you explicitly -use `holding'- again at the end.

Now that Clyde mentions this, I think it was the automatic restore that led me to favor using tempfile over the combination of preserve and restore, preserve in applications like this one. When I'm writing a general program in an ado-file that I expect to call from other programs, then the automatic restore can be enough of an advantage to change my approach. But most often when a do-file fails, I'd like to see what the data was like at the point of failure, which the automatic restore would prevent. in general, I experience more program failures of do-files than I experience opportunities to write general programs callable from others in ado-files.