Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculating averages at industry level after removing focal firm.

    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

  • #2
    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.

    Code:
    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:

    Code:
    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.

    Comment


    • #3
      Clyde - thanks, this really helps.

      Comment


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

        Code:
        rangestat Sales, by(Industry) interval(Year 0 0)  excludeself
        would not work here.

        Comment


        • #5
          Thanks Nick. This code works.

          Comment


          • #6
            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!

            Comment


            • #7
              Indeed. I would like a syntax allowing

              Code:
               
               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.

              Comment

              Working...
              X