Weighted*Average for a Group Excluding the ith member of that group

[Lucas Balaminut]

Hello, I hope you can help me with a question.

I have a panel data with variables year, firm, industry, weight and indepvar.
For each observation i, I want to find out what is the weighted average of indepvar for the industry of the observation i, by year,but removing that firm i from the calculation of the weighted average of indepvar. The idea here is to have a weighted average of indepvar for the particular industry of firm i, in a given year, but pretending firm i is not part of that Industry. The weighted average must pretend firm i is not part of that industry.

I know how to create the weighted average of indepvar by year by industry:

bys year industry: asgen weighted_average_IndepVar = indepvar, w(weight)

But that is how it should not be. This code includes frim i in the calculation of the weighted average indepvar by industry by year.
I do not know how to exclude the current observation i from the calculation.

I'm attaching a pet data file that shows what the weighted average should and should not be.
(The original dataset I'm working on has over 50 thousand firms, so creating flag columns for each firm is not really an option).

I really appreciate any help.

Thanks so much!

Lucas B.

[Clyde Schechter]

If your data are not imported into Stata, it is premature to be asking for help with code. If they are in Stata, then you should post your Stata data set example using the -dataex- command. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Please read the Forum FAQ before your next post so that you will be aware of this and other suggestions that make your posts more informative and more helpful to those who want to help you. You will also learn there that attachments are strongly discouraged: people are rightly hesitant to download attachments from strangers.

All of that said, what you want to do can be done as follows:

by industry year, sort: egen numerator = total(weight*indepvar)
by industry year: egen denominator = total(weight)
replace numerator = numerator - weight*indepvar
replace denominator = denominator - weight
gen weighted_mean_excluding_self = numerator/denominator

[Lucas Balaminut]

Thanks so much Clyde! I will definitely read the rules carefully before posting again.