Export cross tab with summary statistics

[John Kirk]

Hi everyone,

this seems like a super obvious question that I'm sure somebody has asked before, but for the life of me, I can't find exactly what I'm looking for. In essence, what I am trying to do is simply export a table in which the cells are the means of a variable for a cross-tab of two other variables. Taking the auto dataset for a super simple example:

sysuse auto, clear
tab rep78 foreign, sum(mpg) means
*ignore the missing values

I would like to export that table to either excel or latex. I have tried using user written commands such as esttab (which wouldn't let me use the "sum" option), asdoc (which gave me the frequencies in the cells instead of the mean value), and tab2xl (which got me closest but also had the standard deviation in each cell which I don't want [the option of "tab, sum() means" was disabled]).

Any help that anyone here could provide would be highly appreciated!
Thanks so much,
John

[Andrew Musau]

You can collapse the data to get the table. For the column totals, if you need them, you need to create weighting variables based on the frequency within each category.

sysuse auto, clear
tab rep78 foreign, sum(mpg) means
*USING COLLAPSE
separate mpg, by(foreign)
egen total= rowtotal(mpg?)
collapse mpg? total, by(rep78)
drop if missing(rep78)

Res.:

. tab rep78 foreign, sum(mpg) means

                          Means of Mileage (mpg)

    Repair |
    Record |      Car type
      1978 |  Domestic    Foreign |     Total
-----------+----------------------+----------
         1 |        21          . |        21
         2 |    19.125          . |    19.125
         3 |        19  23.333333 | 19.433333
         4 | 18.444444  24.888889 | 21.666667
         5 |        32  26.333333 | 27.363636
-----------+----------------------+----------
     Total | 19.541667  25.285714 | 21.289855



. l

     +--------------------------------------+
     | rep78      mpg0      mpg1      total |
     |--------------------------------------|
  1. |     1        21         .         21 |
  2. |     2    19.125         .     19.125 |
  3. |     3        19   23.3333   19.43333 |
  4. |     4   18.4444   24.8889   21.66667 |
  5. |     5        32   26.3333   27.36364 |
     +--------------------------------------+

[Attaullah Shah]

Nice trick by Andrew Musau
You can add labels to the relevant variables and export them with asdoc.

label var mpg0 Domestic
label var mpg1 Foreign
label var total Total
asdoc list, replace save(doc.doc) label

[John Kirk]

Oh wow, that's so clever. Thanks Andrew Musau! And thanks for the asdoc help Attaullah Shah