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
[email protected]
*
* 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/
