Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

st: RE: Two-way table


From   "Nick Cox" <n.j.cox@durham.ac.uk>
To   <statalist@hsphsun2.harvard.edu>
Subject   st: RE: Two-way table
Date   Sat, 2 Aug 2003 14:16:10 +0100

Friedrich Huebler
 
> I would like to make a two-way table that lists the number of
> observations per row in the last column and the row percentages in
> the remaining columns. For example, with the auto data we can make
> this table:
> 
> . tab rep78 foreign, row
> 
>     Repair |
>     Record |       Car type
>       1978 |  Domestic    Foreign |     Total
> -----------+----------------------+----------
>          1 |         2          0 |         2 
>            |    100.00       0.00 |    100.00 
> -----------+----------------------+----------
>          2 |         8          0 |         8 
>            |    100.00       0.00 |    100.00 
> -----------+----------------------+----------
>          3 |        27          3 |        30 
>            |     90.00      10.00 |    100.00 
> -----------+----------------------+----------
>          4 |         9          9 |        18 
>            |     50.00      50.00 |    100.00 
> -----------+----------------------+----------
>          5 |         2          9 |        11 
>            |     18.18      81.82 |    100.00 
> -----------+----------------------+----------
>      Total |        48         21 |        69 
>            |     69.57      30.43 |    100.00 
> 
> Instead, I would like this table:
> 
>     Repair |
>     Record |       Car type
>       1978 |  Domestic    Foreign |     Total
> -----------+----------------------+----------
>          1 |    100.00       0.00 |         2 
>          2 |    100.00       0.00 |         8 
>          3 |     90.00      10.00 |        30 
>          4 |     50.00      50.00 |        18 
>          5 |     18.18      81.82 |        11 
> -----------+----------------------+----------
>      Total |     69.57      30.43 |        69 
> 
> The closest I could come to this table is with the following
> commands:
> 
> . gen domestic = 100*(foreign==0)
> . replace foreign = 100*foreign
> . bysort rep78: egen counter = count(rep78)
> . format domestic foreign %6.2f
> . format counter %2.0f
> . tabstat domestic foreign counter, by(rep78) format
> 
>    rep78 |  domestic   foreign   counter
> ---------+------------------------------
>        1 |    100.00      0.00         2
>        2 |    100.00      0.00         8
>        3 |     90.00     10.00        30
>        4 |     50.00     50.00        18
>        5 |     18.18     81.82        11
> ---------+------------------------------
>    Total |     69.57     30.43        20
> 
> The cell in the lower right corner should contain the total 
> number of
> observations, that is 69 instead of 20. How can this be done?

Interesting challenge. I played around with this for 
a while and produced two solutions worth reporting. It 
is striking that you could get so close with 
a few lines of interactive code, yet 
getting closer seems a lot more work. 

On the other hand, it sounds as if you want to do this 
repeatedly, so a more general program seems desired. 

There is probably some simpler way I can't spot. 

My first solution was a wrapper for -table-. 

. fhtable rep78 foreign, format(%9.2f)

----------------------------------------------------------
Repair    |
Record    |
1978      | mean(foreign1)  mean(foreign2)      N(foreign)
----------+-----------------------------------------------
        1 |         100.00            0.00               2
        2 |         100.00            0.00               8
        3 |          90.00           10.00              30
        4 |          50.00           50.00              18
        5 |          18.18           81.82              11
          | 
    Total |          69.57           30.43              69
----------------------------------------------------------

On the other hand you can only have four groups! (One 
variable is needed for counts.) Hence this fails: 

. fhtable foreign rep78
too many stats()
r(103);

My second solution was a wrapper for -list-, which works 
both ways: 

. fhlist foreign rep78

  +--------------------------------------------------------+
  | foreign      1       2       3       4       5   Total |
  |--------------------------------------------------------|
  |       0   4.17   16.67   56.25   18.75    4.17      48 |
  |       1   0.00    0.00   14.29   42.86   42.86      21 |
  |--------------------------------------------------------|
  |   Total   2.90   11.59   43.48   26.09   15.94      69 |
  +--------------------------------------------------------+

. fhlist rep78 foreign

  +------------------------------------+
  | rep78   Domestic   Foreign   Total |
  |------------------------------------|
  |     1     100.00      0.00       2 |
  |     2     100.00      0.00       8 |
  |     3      90.00     10.00      30 |
  |     4      50.00     50.00      18 |
  |     5      18.18     81.82      11 |
  |------------------------------------|
  | Total      69.57     30.43      69 |
  +------------------------------------+

Here are the programs: 

program fhtable
        version 8
        syntax varlist(min=2 max=2 numeric) [if] [in] [, *] 
        
        tokenize `varlist' 
        args rowvar colvar 
        
        marksample touse 
        preserve 
        qui keep if `touse' 
        qui tab `colvar', gen(`colvar')
        
        qui foreach v of var `colvar'? { 
                replace `v' = 100 * `v' 
        }

        unab pcvars : `colvar'? 
        
        foreach v of var `pcvars' { 
                local carg "`carg' mean `v'"
        }       
                
        table `rowvar', c(`carg' count `colvar') row `options' 
end 

program fhlist, sort 
	version 8
	syntax varlist(min=2 max=2 numeric) [if] [in] [, *] 
	
	tokenize `varlist' 
	args rowvar colvar 
	
	marksample touse
	qui count if `touse' 
	if r(N) == 0 error 2000
	
	tempname stub 
	qui tab `colvar' if `touse', gen(`stub')
	
	sort `touse' `rowvar' 
	qui { 
		foreach v of var `stub'* { 
			local label : variable label `v' 
			local label = ///
			substr(`"`label'"', index(`"`label'"', "==") + 2, .)
			capture local ilabel = floor(`label') 
			if _rc == 0 { 
				if `ilabel' == `label' { 
					char `v'[varname] "`ilabel'"
				}	
				else char `v'[varname] `"`label'"' 
			} 	
			else char `v'[varname] `"`label'"' 
			by `touse' `rowvar' : replace `v' = sum(100 * `v')
			by `touse' `rowvar' : replace `v' = `v'[_N] / _N 
		}

		tempvar count tag tag2 Rowvar 
		by `touse' `rowvar' : gen `count' = _N 
		char `count'[varname] "Total" 

		by `touse' `rowvar': gen byte `tag' = !(_n == 1 & `touse') 
		sort `tag' `rowvar' 
		count if `tag' == 0  
		local ntag = r(N) 
		local last = `ntag' + 1 

		foreach v of var `stub'* { 
			su `v' if `touse', meanonly 
			replace `v' = r(mean) in `last' 
		} 

		count if `touse' 
		replace `count' = r(N) in `last' 
		
		gen byte `tag2' = _n <= `ntag'
		replace `tag' = 0 in `last' 
		
		gen `Rowvar' = string(`rowvar') 
		char `Rowvar'[varname] "`rowvar'" 

                replace `Rowvar' = "Total" in `last' 
	} 	

	format `stub'* %9.2f 
	list `Rowvar' `stub'* `count' if !`tag', ///
	sepby(`tag2') subvarname noobs `options' 
end 

Nick 
n.j.cox@durham.ac.uk 

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index