Calculating Industry Growth rate for each year & Observation

[Mabel Costa]

Hi!
I am fairly new to STATA. I need to calculate INDUSTRY sales growth rate for each observation (the sales growth rate command doesn't work for the following situation). To make you better understand my problem kindly see below.

Currently this is what I have -

gvkey fyear sale sic IndCode sumSale
4597 1974 3.742 100 10 241.172
10802 1974 237.43 100 10 241.172
4597 1975 3.549 100 10 4314.787
5416 1975 3.176 100 10 4314.787
1464 1975 33.714 100 10 4314.787
1266 1975 6.656 100 10 4314.787
8596 1975 171.027 100 10 4314.787
2812 1975 843.051 100 10 4314.787
6063 1975 138.342 100 10 4314.787
10802 1975 199.965 100 10 4314.787
9146 1975 43.469 100 10 4314.787
10971 1975 191.768 100 10 4314.787
7986 1975 167.555 100 10 4314.787
9391 1975 7.54 100 10 4314.787
10390 1975 22.589 100 10 4314.787
10884 1975 2186.52 100 10 4314.787
2385 1975 287.535 100 10 4314.787
8057 1975 8.326 100 10 4314.787
2608 1974 218.939 200 20 234.037
4576 1974 1.967 200 20 234.037
1199 1974 13.131 200 20 234.037
5611 1975 13.838 200 20 393.478
8761 1975 40.307 200 20 393.478
11393 1975 43.909 200 20 393.478
4576 1975 3.557 200 20 393.478



What I need is following-
gvkey fyear sale sic IndCode sumSale INUDs_Growth
4597 1974 3.742 100 10 241.172 0
10802 1974 237.43 100 10 241.172 0
4597 1975 3.549 100 10 4314.787 16.8909
5416 1975 3.176 100 10 4314.787 16.8909
1464 1975 33.714 100 10 4314.787 16.8909
1266 1975 6.656 100 10 4314.787 16.8909
8596 1975 171.027 100 10 4314.787 16.8909
2812 1975 843.051 100 10 4314.787 16.8909
6063 1975 138.342 100 10 4314.787 16.8909
10802 1975 199.965 100 10 4314.787 16.8909
9146 1975 43.469 100 10 4314.787 16.8909
10971 1975 191.768 100 10 4314.787 16.8909
7986 1975 167.555 100 10 4314.787 16.8909
9391 1975 7.54 100 10 4314.787 16.8909
10390 1975 22.589 100 10 4314.787 16.8909
10884 1975 2186.52 100 10 4314.787 16.8909
2385 1975 287.535 100 10 4314.787 16.8909
8057 1975 8.326 100 10 4314.787 16.8909
2608 1974 218.939 200 20 234.037 0
4576 1974 1.967 200 20 234.037 0
1199 1974 13.131 200 20 234.037 0
5611 1975 13.838 200 20 393.478 0.68126
8761 1975 40.307 200 20 393.478 0.68126
11393 1975 43.909 200 20 393.478 0.68126
4576 1975 3.557 200 20 393.478 0.68126

Would highly appreciate if anyoen can help me with command

[Nick Cox]

A "divide and conquer" strategy for the problem is to do the calculations just once for each industry code and financial year and then spread the results to other observations with the same code and year. Below I give only the last listing but to understand this it's necessary to study each listing and to follow what is done with egen and under by:.

clear
input gvkey fyear sale sic IndCode sumSale
4597 1974 3.742 100 10 241.172
10802 1974 237.43 100 10 241.172
4597 1975 3.549 100 10 4314.787
5416 1975 3.176 100 10 4314.787
1464 1975 33.714 100 10 4314.787
1266 1975 6.656 100 10 4314.787
8596 1975 171.027 100 10 4314.787
2812 1975 843.051 100 10 4314.787
6063 1975 138.342 100 10 4314.787
10802 1975 199.965 100 10 4314.787
9146 1975 43.469 100 10 4314.787
10971 1975 191.768 100 10 4314.787
7986 1975 167.555 100 10 4314.787
9391 1975 7.54 100 10 4314.787
10390 1975 22.589 100 10 4314.787
10884 1975 2186.52 100 10 4314.787
2385 1975 287.535 100 10 4314.787
8057 1975 8.326 100 10 4314.787
2608 1974 218.939 200 20 234.037
4576 1974 1.967 200 20 234.037
1199 1974 13.131 200 20 234.037
5611 1975 13.838 200 20 393.478
8761 1975 40.307 200 20 393.478
11393 1975 43.909 200 20 393.478
4576 1975 3.557 200 20 393.478
end

list, sepby(fyear IndCode)
egen tag = tag(IndCode fyear)
bysort tag IndCode (fyear) : gen Growth = cond(_n == 1, 0, (sumS - sumS[_n-1]) / sumS[_n-1]) if tag
list , sepby(tag IndCode)
bysort IndCode fyear (Growth) : replace Growth = Growth[1]
list fyear IndCode sumSale Growth , sepby(IndCode fyear)

     +---------------------------------------+
     | fyear   IndCode    sumSale     Growth |
     |---------------------------------------|
  1. |  1974        10    241.172          0 |
  2. |  1974        10    241.172          0 |
     |---------------------------------------|
  3. |  1975        10   4314.787   16.89091 |
  4. |  1975        10   4314.787   16.89091 |
  5. |  1975        10   4314.787   16.89091 |
  6. |  1975        10   4314.787   16.89091 |
  7. |  1975        10   4314.787   16.89091 |
  8. |  1975        10   4314.787   16.89091 |
  9. |  1975        10   4314.787   16.89091 |
 10. |  1975        10   4314.787   16.89091 |
 11. |  1975        10   4314.787   16.89091 |
 12. |  1975        10   4314.787   16.89091 |
 13. |  1975        10   4314.787   16.89091 |
 14. |  1975        10   4314.787   16.89091 |
 15. |  1975        10   4314.787   16.89091 |
 16. |  1975        10   4314.787   16.89091 |
 17. |  1975        10   4314.787   16.89091 |
 18. |  1975        10   4314.787   16.89091 |
     |---------------------------------------|
 19. |  1974        20    234.037          0 |
 20. |  1974        20    234.037          0 |
 21. |  1974        20    234.037          0 |
     |---------------------------------------|
 22. |  1975        20    393.478    .681264 |
 23. |  1975        20    393.478    .681264 |
 24. |  1975        20    393.478    .681264 |
 25. |  1975        20    393.478    .681264 |
     +---------------------------------------+

Please study the FAQ Advice including #18 (Stata not STATA) and #12 (explain where user-written commands come from and in what sense they didn't work).

[Mabel Costa]

Thank you so much Nick for your quick response