exporting a two ways table with descriptive statistics to excel

[Sara Indelicato]

Hi all!

I need help with exporting the following table to excel for my thesis. The commands putexcel and extab don't seem to support the summarise command accompanied by the tabulate command.

tab nCurrRtg1 Action, summarize(cds) mean freq

For instance, I have used the following code

putexcel A1 = matrix(r(table)',names) using results.xls, replace

and it didn't work and it gave me error "using not allowed"

Moreover, I tried this command and the answer was as below.

estpost tab nCurrRtg1 Action, summarize(cds) mean freq
option summarize() not allowed
r(198);

Thank you for your consideration,

Sara

[William Lisowski]

The summarize command does not return its results in r(table) so your putexcel command failed.

The tabulate ..., summarize command does not return estimation results so your estpost command failed.

The code below demonstrates using the collapse command to replace the data in memory your data with summary statistics based on that data, which can then be reshaped into the a layout similar to the output of the summarize command.

// get sample data
sysuse auto, clear
drop if missing(rep78)
// demonstrate what tabulate reports
tabulate rep78 foreign, summarize(price) mean freq
// reproduce tabulation results
collapse (mean) mean=price (count) frequency=price, by(rep78 foreign)
list if rep78==3, clean
// transform results into a table
rename (mean frequency) value=
reshape long value, i(rep78 foreign) j(stat) string
list if rep78==3, clean
reshape wide value, i(rep78 stat) j(foreign)
rename (value0 value1) (domestic foreign)
list, clean noobs
// use export excel to write data to excel
export excel using results.xlsx, firstrow(variables) replace

You can copy this code into the Do-file Editor window and run it, giving the following output in the Results window.

. // get sample data
. sysuse auto, clear
(1978 Automobile Data)

. drop if missing(rep78)
(5 observations deleted)

. // demonstrate what tabulate reports
. tabulate rep78 foreign, summarize(price) mean freq

                      Means and Frequencies of Price

    Repair |
    Record |      Car type
      1978 |  Domestic    Foreign |     Total
-----------+----------------------+----------
         1 |   4,564.5          . |   4,564.5
           |         2          0 |         2
-----------+----------------------+----------
         2 | 5,967.625          . | 5,967.625
           |         8          0 |         8
-----------+----------------------+----------
         3 | 6,607.074  4,828.667 | 6,429.233
           |        27          3 |        30
-----------+----------------------+----------
         4 | 5,881.556  6,261.444 |   6,071.5
           |         9          9 |        18
-----------+----------------------+----------
         5 |   4,204.5  6,292.667 |     5,913
           |         2          9 |        11
-----------+----------------------+----------
     Total |  6,179.25  6,070.143 | 6,146.043
           |        48         21 |        69

. // reproduce tabulation results
. collapse (mean) mean=price (count) frequency=price, by(rep78 foreign)

. list if rep78==3, clean

       rep78    foreign      mean   freque~y  
  3.       3   Domestic   6,607.1         27  
  4.       3    Foreign   4,828.7          3  

. // transform results into a table
. rename (mean frequency) value=

. reshape long value, i(rep78 foreign) j(stat) string
(note: j = frequency mean)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                        8   ->      16
Number of variables                   4   ->       4
j variable (2 values)                     ->   stat
xij variables:
               valuefrequency valuemean   ->   value
-----------------------------------------------------------------------------

. list if rep78==3, clean

       rep78    foreign        stat     value  
  5.       3   Domestic   frequency        27  
  6.       3   Domestic        mean   6,607.1  
  7.       3    Foreign   frequency         3  
  8.       3    Foreign        mean   4,828.7  

. reshape wide value, i(rep78 stat) j(foreign)
(note: j = 0 1)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                       16   ->      10
Number of variables                   4   ->       4
j variable (2 values)           foreign   ->   (dropped)
xij variables:
                                  value   ->   value0 value1
-----------------------------------------------------------------------------

. rename (value0 value1) (domestic foreign)

. list, clean noobs

    rep78        stat   domestic   foreign  
        1   frequency          2         .  
        1        mean    4,564.5         .  
        2   frequency          8         .  
        2        mean    5,967.6         .  
        3   frequency         27         3  
        3        mean    6,607.1   4,828.7  
        4   frequency          9         9  
        4        mean    5,881.6   6,261.4  
        5   frequency          2         9  
        5        mean    4,204.5   6,292.7  

. // use export excel to write data to excel
. export excel using results.xlsx, firstrow(variables) replace
file results.xlsx saved

And below is a screen shot of the Excel worksheet.

[Sara Indelicato]

Thanks