Calculating a weighted group mean, but not collapsing the data

[johnkim]

Dear all,
Hi, I'm interested in calculating a weighted group mean without collapsing the data. If we say the data is x and the weight is w, x or w (or both) can be missing for each observation. I want to use only the observations with nonmissing x and w to calculate a weighted group mean. I'm surprised egen does not allow for this and I was wondering if there's an efficient way of doing this.
I'd really appreciate any and all help!

Best,
John

[Friedrich Huebler]

You can do this with a loop.

sysuse auto
bysort rep78: egen mean1 = mean(mpg)
gen mean2 = .
levelsof rep78, local(levels)
foreach l of local levels {
  sum mpg [aw=weight] if rep78==`l'
  replace mean2 = r(mean) if rep78==`l'
}

[Nick Cox]

No loop is needed. Using the auto data, as in Friedrich's example:

 
egen num = total(mpg * weight), by(rep78) 
egen den = total(weight), by(rep78) 
gen mean3 = num/den

For messier problems with missings, although the answer isn't different in this case:

 
egen num2 = total(mpg * weight * !missing(mpg, weight)), by(rep78) 
egen den2 = total(weight * !missing(mpg, weight)), by(rep78) 
gen mean4 = num/den

Note that using egen is not efficient if you mean faster, as egen just fires up code that needs to be interpreted. To make it faster, drill down to use by:.

[johnkim]

Dear Friedrich and Nick,
Thank you very much for your suggestions. They are exactly what I needed. Thank you again!

Best,
John