Generate new variable--percentage change

[Budu Gulo]

My data set has three variables: year, company, and profit. How can I generate a new variable, say pchange, which would contain percentage change in profit for each company on a yearly basis?

I tried the following code.

. sort company year

. bysort company:gen pchange=100*(profit[_n]-profit[_n-1])/profit[_n-1]

Is it the right approach?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 company float(profit year)
"A"  100 1990
"B" 1000 1990
"C"   10 1990
"D"  500 1990
"E"  700 1990
"F"   80 1990
"G"  900 1990
"H"   60 1990
"I"   50 1990
"A"  700 1991
"B"   80 1991
"C"  900 1991
"D"   60 1991
"E"   50 1991
"F"  100 1991
"G" 1000 1991
"H"   10 1991
"I"  500 1991
"J"  700 1991
"A"   80 1992
"B"  500 1992
"C"  700 1992
"D"   80 1992
"E"  900 1992
"F"   60 1992
"G"   50 1992
"H"  700 1992
"I"   80 1992
"J"  900 1992
"K"   60 1992
"A"   80 1993
"B"  900 1993
"C"   60 1993
"D"   50 1993
"E"  100 1993
"F" 1000 1993
"G"   10 1993
"H"  500 1993
"J"  700 1993
"K"   80 1993
"A"  900 1994
"B"   60 1994
"C"   80 1994
"D"  900 1994
"E"   60 1994
"F"   50 1994
"G"  100 1994
"H" 1000 1994
"J"   10 1994
"K"  500 1994
"L"  700 1994
"A"   80 1995
"B"   80 1995
"C"  900 1995
"D"   60 1995
"E"   50 1995
"F"  100 1995
"G" 1000 1995
"H"   10 1995
"J"  500 1995
"K"  700 1995
"L"   80 1995
end

[William Lisowski]

Your approach will work. It can be shortened to a single command - the bysort command will perform any necessary sorting, and enclosing year in parentheses causes bysort to sort by company and year, but ignore year in constructing the by-groups.

. bysort company (year): gen pchange=100*(profit[_n]-profit[_n-1])/profit[_n-1]
(12 missing values generated)

. format pchange %9.2f

. list if company<="B", sepby(company)

     +-----------------------------------+
     | company   profit   year   pchange |
     |-----------------------------------|
  1. |       A      100   1990         . |
  2. |       A      700   1991    600.00 |
  3. |       A       80   1992    -88.57 |
  4. |       A       80   1993      0.00 |
  5. |       A      900   1994   1025.00 |
  6. |       A       80   1995    -91.11 |
     |-----------------------------------|
  7. |       B     1000   1990         . |
  8. |       B       80   1991    -92.00 |
  9. |       B      500   1992    525.00 |
 10. |       B      900   1993     80.00 |
 11. |       B       60   1994    -93.33 |
 12. |       B       80   1995     33.33 |
     +-----------------------------------+

[Nick Cox]

William is quite right but if there are gaps it is better to tsset or xtset the data and use

 
 100*D.profit/L.profit

See

help tsset 
help varlist

[Budu Gulo]

Thanks a lot William Lisowski and Nick Cox !

[Bart Rue]

Hello, I am struggling with something that is similar to this.
I have variables that change on a yearly basis, but as monthly variables (This is because ESG scores are yearly and my return is monthly). What I am trying to get is ESG momentum thus ((value_ESG-L.value_ESG)/L.value_ESG)*100. However, this does not work because ESG does not change monthly and then it says that my data is not sorted. It would not make sense to sort it to monthly because then 11 months out of twelve I would have a change of 0. Does anyone know how to do this?

[William Lisowski]

My attention was drawn to this unanswered question from a year ago. For those whose search leads them to this topic, here's an approach to the problem.

. sort firm mdate

. xtset firm mdate

Panel variable: firm (strongly balanced)
 Time variable: mdate, 2011m1 to 2012m12
         Delta: 1 month

. generate M = 100 * (value - L12.value) / L12.value
(24 missing values generated)

. list, separator(12) noobs

  +-----------------------------+
  | firm     mdate   value    M |
  |-----------------------------|
  |   42    2011m1      50    . |
  |   42    2011m2      50    . |
  |   42    2011m3      50    . |
  |   42    2011m4      50    . |
  |   42    2011m5      50    . |
  |   42    2011m6      50    . |
  |   42    2011m7      50    . |
  |   42    2011m8      50    . |
  |   42    2011m9      50    . |
  |   42   2011m10      50    . |
  |   42   2011m11      50    . |
  |   42   2011m12      50    . |
  |-----------------------------|
  |   42    2012m1      60   20 |
  |   42    2012m2      60   20 |
  |   42    2012m3      60   20 |
  |   42    2012m4      60   20 |
  |   42    2012m5      60   20 |
  |   42    2012m6      60   20 |
  |   42    2012m7      60   20 |
  |   42    2012m8      60   20 |
  |   42    2012m9      60   20 |
  |   42   2012m10      60   20 |
  |   42   2012m11      60   20 |
  |   42   2012m12      60   20 |
  |-----------------------------|
  |   66    2011m1      90    . |
  |   66    2011m2      90    . |
  |   66    2011m3      90    . |
  |   66    2011m4      90    . |
  |   66    2011m5      90    . |
  |   66    2011m6      90    . |
  |   66    2011m7      90    . |
  |   66    2011m8      90    . |
  |   66    2011m9      90    . |
  |   66   2011m10      90    . |
  |   66   2011m11      90    . |
  |   66   2011m12      90    . |
  |-----------------------------|
  |   66    2012m1      99   10 |
  |   66    2012m2      99   10 |
  |   66    2012m3      99   10 |
  |   66    2012m4      99   10 |
  |   66    2012m5      99   10 |
  |   66    2012m6      99   10 |
  |   66    2012m7      99   10 |
  |   66    2012m8      99   10 |
  |   66    2012m9      99   10 |
  |   66   2012m10      99   10 |
  |   66   2012m11      99   10 |
  |   66   2012m12      99   10 |
  +-----------------------------+

.