generate mean value of a locality excluding the focal firm and firms in the same industry as the focal firm

[Yiyi Yang]

Dear Statalist,

I am trying to generate the mean value of a locality, excluding the focal firm and firms in the same industry as the focal firms. Specifically, I have 100 firms located in 10 provinces across 13 industries. I attempt to calculate the average leverage of all firms located in province j, excluding the focal firm i and its industry rivals.

Any suggestions would be highly appreciated!

Best regards,

Yiyi

[Nick Cox]

The average of other firms in different industries in the same province (and in the same year, presumably) can be calculated in stages.

egen double totalG = total(whatever), by(province year)
egen double totalL = total(whatever), by(province industry year)

egen countG = count(whatever), by(province year)
egen countL = count(whatever), by(province industry year)

gen double wanted = (totalG - totalL) / (countG - countL)

because the mean of others is

(*) (total of all MINUS total in this group) / (count of all MINUS count in this group)

Note that the egen functions total() and count() ignore missings, usually as researchers would prefer.

You should able to modify this recipe for different needs, e.g. data for quarterly or monthly dates, or even averages regardless of time. The principle (*) is a very useful identity.

See also https://www.stata.com/support/faqs/d...ng-properties/

[Yiyi Yang]

Thank you so much, Nick!