How to export series of crosstabs with labels to Excel?

[AJ Dunne]

I would like to be able to export a series of two-way tables, with contents as a percent of the column total, with row and column labels into excel. It seems that there are two ways I could potentially do this- 1) save a table as a matrix and then use putexcel to export it, or 2) use collapse to reduce my dataset to the variables of interest and use export excel from there. However, I'm having difficulty completing either of these options in a way that preserves the row and column labels in the table.

For example, below is an example of the form of a table that I would like to be export.

webuse citytemp2, clear
(City Temperature Data)

. 
. tab region agecat, column nofreq

    Census |              agecat
    Region |     19-29      30-34        35+ |     Total
-----------+---------------------------------+----------
        NE |      9.07      26.27      27.82 |     17.36 
   N Cntrl |     31.95      29.11      22.56 |     29.71 
     South |     27.42      21.52      32.33 |     26.15 
      West |     31.56      23.10      17.29 |     26.78 
-----------+---------------------------------+----------
     Total |    100.00     100.00     100.00 |    100.00

Does anyone have a recommendation for the best way to do this?

[Isaac Maddow-Zimet]

I don't know the best way, but the way I typically do this is with a combination of -estpost- (part of the excellent and very useful -estout- package written by Ben Jann and available through SSC), which stores results from twoway tabulations in a matrix while preserving row and column names, and -unstack- which is a very simple helper program I wrote (see details here) to make the matrix that -estpost- spits out a bit easier to work with.

So with your example, I would first install estout and unstack:

ssc install estout
net install unstack, from("https://raw.githubusercontent.com/imaddowzimet/StataPrograms/master/")

and then type the following:

webuse citytemp2, clear
estpost tab region agecat
unstack
* Results will be in a matrix called col, which you can export with putexcel as normal
mat list col

You can then export the matrix called "col" as normal with putexcel (making sure to specify the names option to export the row and column names of the matrix).

[AJ Dunne]

Thanks very much Isaac for answering my question and writing the helper program! That's exactly what I'm looking for.

[Isaac Maddow-Zimet]

No problem at all - glad it came in handy.

[Mungai Mwangi]

No problem at all - glad it came in handy.

Thanks Isaac for this useful command, it came in handy. I wanted to check with you on whether there's a quick fix for the row labels since at the moment we only get the column labels in the output. Is there any way we could export both when using putpdf?

[Attaullah Shah]

You can use asdoc to send the tabulation results to MS Word and asdocx to send the results to either Excel, Word, or LaTeX. Here is an example

ssc install asdoc
webuse citytemp2, clear
asdoc tab region agecat, column nofreq replace

[Noof Abdulhadi]

Nick Cox Attaullah Shah Hi, do you know how can I use svy command with asdoc command to produce two-way tabulation in Word doc.?

I used the the following syntax:

asdoc tab gender nationality, nokey column replace

asdoc svy: tab gender nationality, nokey column replace (this syntax gives the same results of syntax without svy. While when I use svy without asdoc, it gives me different results).


Thank you,

[Katy Quevedo]

I don't know the best way, but the way I typically do this is with a combination of -estpost- (part of the excellent and very useful -estout- package written by Ben Jann and available through SSC), which stores results from twoway tabulations in a matrix while preserving row and column names, and -unstack- which is a very simple helper program I wrote (see details here) to make the matrix that -estpost- spits out a bit easier to work with.

So with your example, I would first install estout and unstack:

ssc install estout
net install unstack, from("https://raw.githubusercontent.com/imaddowzimet/StataPrograms/master/")

and then type the following:

webuse citytemp2, clear
estpost tab region agecat
unstack
* Results will be in a matrix called col, which you can export with putexcel as normal
mat list col

You can then export the matrix called "col" as normal with putexcel (making sure to specify the names option to export the row and column names of the matrix).

Hello
Is there any way to do this using svy?

[Isaac Maddow-Zimet]

Hi Katy,

Apologies for the delay in responding - this should work normally with the svy prefix. So to give a worked example (creating a fake sample design just to show how it works):

* Load data
webuse citytemp2, clear

* Define fake sample design (you won't need to do this step)
gen n = 1
svyset _n [pweight = n]

* Crosstab
estpost svy: tab region agecat
unstack

* Results are stored in named matrices listed after unstack - this is showing the col matrix
mat list col