Generating a YoY growth variable for panel data

[Anupam Ghosh]

I have county-wise unemployment data from 1990-2020. I want to create a YoY growth rate of unemployment for all counties. Does the following capture the growth rate?

sort fips year
by fips (year), sort: gen Unemp_RateChg = 100*(D.Unemp_Rate/L.Unemp_Rate),before(Unemp_Rate)

[Carlo Lazzaro]

Anupam:
I'm surely old-fogey in what follows, but I'd go this way:

use "https://www.stata-press.com/data/r18/nlswork.dta"
. bysort idcode (year): gen wanted= ln_wage[_n]/ln_wage[_n-1]

. bysort idcode (year): gen wanted_other= (ln_wage[_n]-ln_wage[_n-1])/ln_wage[_n-1]

. list idcode ln_wage year wanted wanted_other if idcode==1

       +-------------------------------------------------+
       | idcode    ln_wage   year     wanted   wanted_~r |
       |-------------------------------------------------|
    1. |      1   1.451214     70          .           . |
    2. |      1    1.02862     71   .7087995   -.2912005 |
    3. |      1   1.589977     72   1.545739    .5457387 |
    4. |      1   1.780273     73   1.119684    .1196844 |
    5. |      1   1.777012     75   .9981682   -.0018319 |
       |-------------------------------------------------|
    6. |      1   1.778681     77   1.000939    .0009392 |
    7. |      1   2.493976     78   1.402149    .4021494 |
    8. |      1   2.551715     80   1.023152    .0231516 |
    9. |      1   2.420261     83   .9484841   -.0515159 |
   10. |      1   2.614172     85    1.08012    .0801199 |
       |-------------------------------------------------|
   11. |      1   2.536374     87   .9702396   -.0297604 |
   12. |      1   2.462927     88   .9710428   -.0289572 |
       +-------------------------------------------------+

.

[Anupam Ghosh]

Hi Carlo,
Thanks for sharing this. Can you please point out what is the mistake which I am making with my code?

[Carlo Lazzaro]

Anupam:
admittedly, I did not test your code.
I used the formulae in #2 in other similar instances.

[Anupam Ghosh]

Carlo,
I checked both the codes and here's a short sample of what I get. The Murder_Percap_chg variable is created by using the code as given in #1. Murder_Percap = (Murder/County_Pop)*100,000.

What I do not understand is why both variables; Murder_Percap_chg and wanted_other do not generate any values for the year 1991. Secondly, for some observations the code in #2 generates values but the not the code in #1 (check year=2015). Even when, they are supposed to be identical.

input double fips float year double Murder float(Murder_Percap Murder_Percap_chg wanted_other)
1001 1990 0 0 . .
1001 1991 1 2.866644 . .
1001 1992 5 13.948558 386.5815 3.865815
1001 1993 3 8.150623 -41.56655 -.4156655
1001 1994 4 10.519118 29.059065 .29059064
1001 1995 5 12.839937 22.062866 .22062865
1001 1996 8 19.989006 55.67838 .5567838
1001 1997 2 4.873413 -75.61953 -.7561953
1001 1998 10 23.86236 389.6437 3.8964374
1001 1999 4 9.356725 -60.78877 -.6078877
1001 2000 2 4.4554343 -52.38255 -.5238254
1001 2001 4 8.712889 95.55644 .9555644
1001 2002 1 2.1367521 -75.47596 -.7547596
1001 2003 3 6.202704 190.28658 1.9028656
1001 2004 0 0 -100 -1
1001 2005 0 0 . .
1001 2006 2 3.8164296 . .
1001 2007 2 3.753965 -1.6367254 -.016367255
1001 2008 7 12.930636 244.45276 2.4445276
1001 2009 0 0 -100 -1
1001 2010 5 9.130586 . .
1001 2015 3 5.464182 . -.4015518
1001 2016 7 12.65777 131.64984 1.3164985
1001 2017 8 14.427933 13.98479 .1398479
1001 2018 6 10.804386 -25.1148 -.25114802
1001 2019 3 5.379333 -50.21159 -.50211585
1001 2020 5 8.905513 65.550514 .6555052
1003 1990 2 2.025583 . .
1003 1991 1 .978713 -51.68241 -.51682407
1003 1992 1 .9404684 -3.9076426 -.03907643
end

[Carlo Lazzaro]

Anupam:
this happens whenever -Murder_Percap[_n-1]- = 0 (as in 1990).
In the following example that exploit your data excerpt (thanks) you can see the issue:

. bysort fips (year): gen wanted_CL= Murder_Percap[_n]/Murder_Percap[_n-1]


. bysort fips (year): gen wanted_other_CL= ( Murder_Percap [_n]- Murder_Percap[_n-1])/ Murder_Percap[_n-1]


. list

     +--------------------------------------------------------------------------------+
     | fips   year   Murder   Murder~p   Murder_~g   wanted_~r   wan~d_CL   want~r_CL |
     |--------------------------------------------------------------------------------|
  1. | 1001   1990        0          0           .           .          .           . |
  2. | 1001   1991        1   2.866644           .           .          .           . |
  3. | 1001   1992        5   13.94856    386.5815    3.865815   4.865815    3.865815 |
  4. | 1001   1993        3   8.150623   -41.56655   -.4156655   .5843345   -.4156655 |
  5. | 1001   1994        4   10.51912    29.05906    .2905906   1.290591    .2905906 |
     |--------------------------------------------------------------------------------|
  6. | 1001   1995        5   12.83994    22.06287    .2206286   1.220629    .2206286 |
  7. | 1001   1996        8   19.98901    55.67838    .5567838   1.556784    .5567838 |
  8. | 1001   1997        2   4.873413   -75.61953   -.7561953   .2438047   -.7561953 |
  9. | 1001   1998       10   23.86236    389.6437    3.896437   4.896437    3.896437 |
 10. | 1001   1999        4   9.356725   -60.78877   -.6078877   .3921123   -.6078877 |
     |--------------------------------------------------------------------------------|
 11. | 1001   2000        2   4.455434   -52.38255   -.5238254   .4761746   -.5238254 |
 12. | 1001   2001        4   8.712889    95.55644    .9555644   1.955564    .9555644 |
 13. | 1001   2002        1   2.136752   -75.47596   -.7547596   .2452404   -.7547596 |
 14. | 1001   2003        3   6.202704    190.2866    1.902866   2.902865    1.902865 |
 15. | 1001   2004        0          0        -100          -1          0          -1 |
     |--------------------------------------------------------------------------------|
 16. | 1001   2005        0          0           .           .          .           . |
 17. | 1001   2006        2    3.81643           .           .          .           . |
 18. | 1001   2007        2   3.753965   -1.636725   -.0163673   .9836327   -.0163673 |
 19. | 1001   2008        7   12.93064    244.4528    2.444528   3.444528    2.444528 |
 20. | 1001   2009        0          0        -100          -1          0          -1 |
     |--------------------------------------------------------------------------------|
 21. | 1001   2010        5   9.130586           .           .          .           . |
 22. | 1001   2015        3   5.464182           .   -.4015518   .5984482   -.4015519 |
 23. | 1001   2016        7   12.65777    131.6498    1.316499   2.316499    1.316499 |
 24. | 1001   2017        8   14.42793    13.98479    .1398479   1.139848    .1398479 |
 25. | 1001   2018        6   10.80439    -25.1148    -.251148    .748852    -.251148 |
     |--------------------------------------------------------------------------------|
 26. | 1001   2019        3   5.379333   -50.21159   -.5021158   .4978842   -.5021158 |
 27. | 1001   2020        5   8.905513    65.55051    .6555052   1.655505     .655505 |
 28. | 1003   1990        2   2.025583           .           .          .           . |
 29. | 1003   1991        1    .978713   -51.68241   -.5168241   .4831759   -.5168241 |
 30. | 1003   1992        1   .9404684   -3.907643   -.0390764   .9609236   -.0390764 |
     +--------------------------------------------------------------------------------+

.

[Anupam Ghosh]

Is there a way to get around this problem? Given that an increase from 0 to 1 indicates a 100% rise in the murder rate.

[Nick Cox]

Given that an increase from 0 to 1 indicates a 100% rise in the murder rate.

No; it doesn't. A fall from 1 to 0 would be a 100% reduction, but the statement can't be reversed. Setting aside the fact that we're counting, a rise from 0.01 to 1 would be a rise of 9900%, one from 1 in a million to 1 would be an even bigger percent rise, and so on. Those simple mathematical facts undermine your statement. The % change from 0 to 1 is indeterminate.

Looking at the numbers in the example you have erratic-seeming fluctuations in small integers, including 0, and it seems unlikely that annual percentage change have much value.

Smart people have wrestled with these problems. A few references:

Cole T. J. 2000. Sympercents: Symmetric percentage differences on the 100 log(e) scale simplify the presentation of log transformed data. Statistics in Medicine 19: 3109–3125. https://onlinelibrary.wiley.com/doi/...8%3E3.0.CO;2-F

Cole T. J., Altman D. G. 2017. Statistics Notes: What is a percentage difference? BMJ 358: j3663. https://doi.org/10.1136/bmj.j3663.

Törnqvist L., Vartia P., Vartia Y. O. 1985. How should relative changes be measured? American Statistician 39: 43–46. https://doi.org/10.2307/2683905.

[Anupam Ghosh]

Nick,

Just as a continuation to the point which you raise. Do you thnk it is a better strategy to have your dependent variable standardized rather than expressing it in annual growth rates?

[Nick Cox]

You opened a new thread at https://www.statalist.org/forums/for...dent-variables

What I would say in answer to #9 is said within, or is entirely consistent with, what is said in that thread by Clyde Schechter . I am not at all a fan of standardisation in this context, a simple summary being that it does not help with any fundamental problems and may indeed obfuscate.

It is good practice here on Statalist not to run multiple threads that are closely related. It can be fine to open a new thread if the focus of enquiry has shifted, but in that case cross-references either way are a really good idea. Much advice on this within the FAQ.

Looking back over this one, I see that we started with unemployment and ended with murder....