Calculating averages at industry level after removing focal firm.

[Shekhar Misra]

I use Stata 14.2. I have a panel data at firm-year level. I want to calculate mean(Sales) at industry-year level, after removing the focal firm. I understand that egen command will allow to do this at industry-year level. But I do not know how to do it after removing the sales of the focal firm. Moreover, the number of firms in a industry is not fixed across the panel as the panel is unbalanced.

Company	Year	Sales	Industry
ABC	1998	10	1
BCD	1998	14	1
CDE	1998	12	1
DEF	1998	67	2
EFG	1998	55	2
FGH	1998	60	2

[Clyde Schechter]

You can do it from first principles. The trick is that the overall average is total divided by count. To exclude the focal observation, just subtract its value from the total and reduce the count by 1.

by industry year, sort: egen total = total(sales)
by industry year: gen avg_sales = (total-sales)/(_N-1)

Or, you can get Robert Picard and Nick Cox and Roberto Ferrer's -rangestat- command from SSC and do it as:

gen zero = 0
rangestat (mean) avg_sales = sales, by(industry year) excludeself interval(zero 0 0)

The creation of the variable zero is a bit kludgy here: if you have other numeric variables in our data besides year, sales, and industry you could just place that variable in the -interval()- option (still followed by two zeroes) instead.

[Shekhar Misra]

Clyde - thanks, this really helps.

[Nick Cox]

I can't test this right now, but I don't see why

rangestat Sales, by(Industry) interval(Year 0 0)  excludeself

would not work here.

[Shekhar Misra]

Thanks Nick. This code works.

[Clyde Schechter]

Yes, Nick is right. My error was putting year into the -by()- grouping. The same variable can't serve in both the -interval()- and -by()- options. But, of course, there is no need to have year in the -by()- option when the interval around it will be year+0 and year-0!

[Nick Cox]

Indeed. I would like a syntax allowing

 
 rangestat Sales, by(Industry) interval(Year)  excludeself

so that the offsets default to 0 0. But Robert Picard, who is first author and did about 99% of the work on the program, hasn't been persuaded of that.