*! $Rev$ $Sep 09 2011$
// gsum calculates the mean, standard deviation, and two versions of a set of quantiles for grouped data.
program gsum, rclass byable(recall)
version 11.1
syntax varlist [if] [fweight aweight pweight iweight], [g0(string)] [g1(string)] ///
[g2(string)] [g3(string)] [g4(string)] [g5(string)] [g6(string)] ///
[g7(string)] [g8(string)] [g9(string)] [g10(string)] [g11(string)] ///
[g12(string)] [g13(string)] [g14(string)] [g15(string)] [g16(string)] ///
[g17(string)] [g18(string)] [g19(string)] [g20(string)] [g21(string)] ///
[g22(string)] [g23(string)] [g24(string)] [g25(string)] ///
[Table] [Save(string)] [GENerate(namelist)] [Quantiles(numlist)]
preserve
*mark sample
marksample touse
*get categorical list, assure they are all integers
quietly : levelsof `varlist' if `touse', local(gvarlist)
local gcount = 0
foreach l in `gvarlist' {
if "`lowestg'" == "" {
local lowestg = `l'
}
local highestg = `l'
capture confirm integer number `l'
if _rc == 7 {
display as error "non-integer value found in `varlist'"
exit 7
}
local ++gcount
}
capture assert `gcount' > 1
if _rc == 9 {
display "only 1 category of `varlist'"
exit 9
}
*figure out the quantiles
capture confirm existence `quantiles'
if _rc == 6 {
local qlist "25 50 75"
}
else {
foreach n in `quantiles' {
capture assert `n' > 0 & `n' < 1
if _rc == 9 {
display as error "quantiles need to be between 0 and 1"
exit 9
}
local p = regexr("`n'","\.","")
foreach k in 1 2 3 4 5 6 7 8 9 {
if "`p'" == "`k'" {
local p "`p'0"
}
}
local qlist "`qlist' `p' "
}
}
*specifiy the temporary scalars
tempname count totw mean var sd mn mx
foreach q in `qlist' {
tempname qi`q' qm`q'
}
forvalue i = 0/25 {
tempname lower`i' upper`i' midpt`i' range`i'
}
*specify the temporary variables
tempvar w fw sfw upper lower midpt range w_midpt dev2 quantile
*assure that there is only one variable to get stats of
local k = 0
foreach v in `varlist' {
local ++k
}
if `k' > 1 {
display as error ///
"gsum only calcuates point estimates for a single variable"
exit 9
}
*assure that the categorical variable only has values between 0 and 25
quietly : sum `gvar' if `touse'
capture assert r(min) > 0 & r(max) < 26
if _rc == 9 {
display as error ///
"your group variable needs numeric categories that range from 0-25"
exit 9
}
*capture ranges from value labels if they exist
forvalues l = 0/25 {
local checklist "`checklist'`g`l''"
}
capture confirm existence `checklist'
if _rc == 6 {
local labelist : value label `varlist'
capture confirm existence `labelist'
if _rc == 6 {
display as error "need to specify ranges in either the options or as value labels"
}
else {
foreach l in `gvarlist' {
local g`l' : label `labelist' `l'
}
}
}
*create category macros
foreach l in `gvarlist' {
tokenize `g`l'', parse(-)
if "`2'" == "-" {
*parse ok
}
else {
display as error "please provide a range for group `l'"
exit 9
}
capture confirm number `1'
if _rc == 7 {
display as error ///
"`1' not a number, please provide numeric quantity for group `l' lower bound"
exit 9
}
capture confirm number `3'
if _rc == 7 {
display as error ///
"`3' not a number, please provide numeric quantity for group `l' higher bound"
exit 9
}
if `3' > `1' {
*range ok
}
else if `3' < `1' {
display as error ///
"lower bound is higher than upper bound for group `l'"
exit 9
}
else if `3' == `1' {
display as error ///
"lower bound is equal to upper bound for group `l'"
exit 9
}
scalar `lower`l'' = `1'
scalar `upper`l'' = `3'
scalar `range`l'' = `3' - `1'
scalar `midpt`l'' = ((`3' - `1')/2) + `1'
if `l' == `lowestg' {
scalar `mn' = `1'
}
if `l' == `highestg' {
scalar `mx' = `3'
}
}
*assure that categories don't overlap
foreach l in `gvarlist' {
foreach k in `gvarlist' {
if `l' == `k' {
*skip
}
else if `l' < `k' {
capture assert `upper`l'' <= `lower`k''
if _rc == 9 {
display as error "group definitions `l' and `k' overlap"
exit 9
}
}
else if `k' < `l' {
capture assert `upper`k'' <= `lower`l''
if _rc == 9 {
display as error "group definitions `k' and `l' overlap"
exit 9
}
}
}
}
*drop missing categories of varlist
quietly : drop if `varlist' == .
*create or use the weight variable
if "`weight'" == "" {
quietly : gen `w' = 1 if `touse'
}
else {
quietly : gen `w' `exp' if `touse'
}
*gen casecount variable
quietly : gen n = 1 if `touse'
quietly : sum n
scalar `count' = r(sum)
*get the sum of the weights for each value of varlist
collapse (sum) `w' n if `touse', by(`varlist')
sort `varlist'
*calculate CDF
quietly : sum `w'
scalar `totw' = r(sum)
quietly : gen `fw' = `w'/`totw'
quietly : gen `sfw' = sum(`fw')
*initialize necessary variables
quietly : gen `lower' = .
quietly : gen `upper' = .
quietly : gen `midpt' = .
quietly : gen `range' = .
*fill in values
foreach l in `gvarlist' {
foreach stat in lower upper midpt range {
quietly : replace ``stat'' = ``stat'`l'' if `varlist' == `l'
}
}
*calculation of the mean
quietly : gen `w_midpt' = `midpt' * `w'
quietly : sum `w_midpt'
scalar `mean' = r(sum)/`totw'
*calculation of variance and standard deviation
quietly : gen `dev2' = ((`midpt' - `mean')^2)*`w'
quietly : sum `dev2'
scalar `var' = r(sum) / (`totw' - 1)
scalar `sd' = `var'^.5
*calculate quantiles
foreach q in `qlist' {
*interpolation
quietly : gen `quantile' = ///
`lower' + ((((.`q') - `sfw'[_n-1])/`fw') * `range') ///
if `sfw' >= (.`q') & `sfw'[_n-1] < (.`q')
quietly : sum `quantile'
if r(N) == 1 {
scalar `qi`q'' = r(mean)
}
else if r(N) == 0 {
quietly : replace `quantile' = ///
`upper' - ((( `sfw' - (.`q'))/`fw') * `range') ///
if (`sfw' >= (.`q') & `sfw'[_n-1] < (.`q')) | _n == 1
quietly : sum `quantile'
scalar `qi`q'' = r(mean)
}
drop `quantile'
*midpoint of the category at or above quantile
quietly : gen `quantile' = ///
`midpt' if (`sfw' >= (.`q') & `sfw'[_n-1] < (.`q') )
quietly : sum `quantile'
if r(N) == 1 {
scalar `qm`q'' = r(mean)
}
else if r(N) == 0 {
quietly : replace `quantile' = ///
`midpt' if _n == 1
quietly : sum `quantile'
scalar `qm`q'' = r(mean)
}
drop `quantile'
}
*post results to r
return scalar N = `count'
else if "`weight'" != "" {
return scalar sum_W = `totw'
}
foreach stat in mean var sd mn mx {
return scalar `stat' = ``stat''
}
foreach q in `qlist' {
return scalar qm`q' = `qm`q''
return scalar qi`q' = `qi`q''
}
*display results
display _newline as text "Grouped Data Summary Statistics" _newline
display as text %12s abbrev("Variable",12) _col(14) "{c |}" ///
_col(22) "N" _col(29) "Mean" _col(40) "Std. Dev." _col(55) "Min" _col(67) "Max"
display as text "{hline 13}" "{c +}" "{hline 60}"
if "`weight'" == "" {
display as text as text %12s abbrev("`varlist'",12) _col(14) "{c |}" ///
_col(15) as result %8.0fc `count' ///
_col(22) as result %10.3f `mean' _col(38) as result %10.3f `sd' ///
_col(45) as result %10.3f `mn' _col(60) as result %10.3f `mx'
}
else if "`weight'" != "" {
display as text as text %12s abbrev("`varlist'",12) _col(14) "{c |}" ///
_col(15) as result %8.0fc `totw' ///
_col(22) as result %10.3f `mean' _col(38) as result %10.3f `sd' ///
_col(45) as result %10.3f `mn' _col(60) as result %10.3f `mx'
}
display _newline as text "Quantiles" _newline
display as text %12s abbrev("Quantile",12) _col(14) "{c |}" ///
_col(17) "Lowest Midpoint at Quantile" _col(50) "Linear Interpolation"
display as text "{hline 13}" "{c +}" "{hline 60}"
foreach q in `qlist' {
if `qm`q'' != . & `qi`q'' != . {
display as text %12s abbrev("0.`q'",12) _col(14) "{c |}" ///
_col(23) as result %10.3f `qm`q'' _col(53) as result %10.3f `qi`q''
}
else {
display as text %12s abbrev("0.`q'",12) _col(14) "{c |}" ///
_col(23) as result "not attainable" _col(53) as result %10.3f "not attainable"
}
}
display as text "{hline 13}" "{c BT}" "{hline 60}"
*rename for value table and renaming
quietly : tostring `lower' `upper', force replace format(%10.0f)
quietly : gen Range = `lower' + "-" + `upper'
rename `midpt' Midpoint
rename `w' Weight
rename `fw' pWeight
rename `sfw' CDF
format Weight pWeight CDF %9.3f
label val `varlist'
if "`table'" == "table" {
display _newline as text "Value Table"
list `varlist' Range Midpoint n Weight pWeight CDF, ///
noobs sep(35) sum(n Weight pWeight)
}
if "`save'" != "" {
keep `varlist' Range Midpoint n Weight pWeight CDF
order `varlist' Range Midpoint n Weight pWeight CDF
quietly : save "`save'", replace
}
*restore data
restore
if "`generate'" != "" {
display _newline "variable `generate' created with value midpoints"
quietly : gen `generate' = .
foreach l in `gvarlist' {
quietly : replace `generate' = `midpt`l'' if `varlist' == `l'
}
}
end