Piece of cake:
clear
* fake data
input id group estimate moe
101 1 36 56
102 1 0 25
103 1 0 28
104 1 0 31
201 2 0 24
202 2 58 62
301 3 0 27
302 3 0 19
303 3 0 22
401 4 0 27
501 5 73 49
502 5 22 26
601 6 48 37
end
* groups 1 and 2 are generic cases of mixed zero and nonzero estimates
* group 3 is all zeroes -- expect MOE = 27
* groups 4 and 6 have only one case -- expect MOE to roll over
* group 5 is a generic case with all nonzeroes
list, sepby(group)
egen tot_est = sum(estimate), by(group)
* sum the MOEs for nonzero estimates
egen sum_moe_sq = sum( moe*moe*(estimate>0) ), by(group)
list, sepby(group)
* isolate out the zero estimate with the largest MOE
bysort group estimate (moe) : replace sum_moe_sq = sum_moe_sq + moe*moe*(estimate==0)*(_n==_N)
list, sepby(group)
* propagate the largest sum_moe_sq to the whole group
* if there were no nonzero estimates, as in group 5, this is innocuous -- no changes are made
* if there were any nonzero estimates, the sum of squared MOEs was modified once
* by the largest MOE^2 of the nonzero estimates, and that's the MOE to use
bysort group (sum_moe_sq) : replace sum_moe_sq = sum_moe_sq[_N]
list, sepby(group)
* take the square roots
gen tot_moe = sqrt(sum_moe_sq)
list, sepby(group)
* c.f. target MOEs where nontrivial
di sqrt(56*56+31*31)
di sqrt(62*62+24*24)
di sqrt(49*49+26*26)
This of course can be wrapped into a program, or even into an -egen- command. And a Stata Tip should be published in Stata Journal on this :).