Re: st: Creating single crosstab table from two separate crosstabs

[Eric Booth <ebooth@ppri.tamu.edu>]

Hi Scott:

One place to start is by taking a look at -collapse- and -contract- for making these sorts of tables.  I'm still not clear on what you want your column variables to show (total/overall freq of non-missings for headroom and foreign by rep78 -- or freq of each level/category of both headroom and foreign  by rep78 ?) , but here are a couple of examples to take a look at:

****************************!
**be sure to install -tab3way- and -tabout- from SSC**
sysuse auto, clear
ta headroom for

************1. overall count
//a. collapse//
     preserve
collapse (count) for headroom, by(rep78)
egen TOTAL = total(for)
outsheet using "table1.xls", replace 
li
     restore

//b. tabout (from SSC) //
tabout rep78 using "table1.xls", append sum mi ///
	c(count for count headroom) h2(tabout table 1) ///
	f(2c) ptotal(both) lines(double) clab(Foreign Headroom) ///
	total(Total) npos(col)  






***************2. categories for each column var

//a. tab3way (from SSC)//
tab3way rep78 headr for, rowtot coltot	
**not as easy to export these results
*-- try looking at Roy Wada's -logout- from SSC



//b.  tabout (from SSC)//

**first, create column labels & stats for tabout options**
/*
  note: it's probably easier just to type these out for 
 the c() and clab() options in tabout, but if you've 
 got a lot more than 5 or 6 categories per var, 
 this code can ease your workload:
*/
ta headroom, g(h_)
ta for, g(f_)
recode h_* f_* (0=.)
qui ta headroom for
foreach z in f h {
	if "`z'"=="f" loc j "c"
	if "`z'"=="h" loc j "r"
forval `z' = 1/`r(`j')' {
	loc clist `clist'  count  `z'_``z''
	loc label`z'``z'': var lab `z'_``z''
	loc label`z'``z'': subinstr local label`z'``z'' "foreign==" ""
	loc label`z'``z'': subinstr local label`z'``z'' "headroom==  " ""
	loc label`z'``z'': subinstr local label`z'``z'' "000" ""
	loc clablist `clablist' `label`z'``z'''
	}
}

**tabout**
tabout rep78 using "table1.xls", append sum mi ///
	c(`clist') h1(tabout table 2) ///
	f(2c) ptotal(both) lines(double) clab(`clablist') ///
	total(Total) npos(col)  h2(| FOREIGN | | HEADROOM | | | |) 
**view**	
di as smcl `"Click to open {browse table1.xls}"'
*************************!
I haven't worked with estpost/estout for these types of tables. 
If these examples don't provide something close to the output you expected, please provide an example of what you want the tables to look like.  

- Eric
__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
ebooth@ppri.tamu.edu

On Mar 21, 2011, at 3:28 PM, Scott Cunningham wrote:

> I am currently trying to automate a number of basic procedures for a
> client, one of which involves calculating crosstabulations for a
> survey questionaire.  The client has a question which can take on
> values of 1, 2 or 3, and he is interested in seeing the joint
> frequency of that value against two other separate variables, but he
> wants to present all of the cross tabs in one table.  So that this is
> concrete, I will outline what I am doing using the auto.dta dataset.
> I want to ultimately have a single table in which each row is the
> values from "rep78", and the columns are "headroom" followed by
> "foreign", and then finally "total", and I've tried repeatedly to do
> this using -estpost- and -estout-, because estpost is able with
> unstack to transpose the values from the saved matrices to create the
> columns the way that I desire. But it does not appear to have an easy
> way to do this using two sets of stored matrices.  Below is my most
> recent effort, which I reproduce only so that someone may have some
> code to help them deiscern what I'm trying.
> 
> estpost tabulate rep78 headroom
> matrix a=e(pct)
> estpost tabulate rep78 foreign
> matrix b=e(pct)
> #delimit ;
> estout matrix(a b) using ./test.tex, style(tex) label notype stats(a )
> unstack replace;
> 
> 
> 
> sincerely,
> 
> Scott Cunningham
> Assistant Professor
> Dept of Economics
> Baylor University
> Waco, Texas 76798-8003
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/



*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/