Creating a variable to express percentage change, but based on a the change from fixed points in time, not [n-1] for example.

[Kai Penfold]

Hey,

Using the help on other threads from Nick Cox I've been able to generate a variable that expresses the percentage change in price of house plants from one month to the next, with the code:

tsset price date
sort date
gen price_change = (price - price[_n-1]) / (price[_n-1])

However, what I'm trying to do is create the percentage change in price from January in 2018 (for the rest of the months in 2018), the change in price from January in 2019 (for the rest of 2019) and the same for 2020 - allowing for the comparison of monthly price changes across three separate years.

Any advice on how I might go about adjusting the code to get the desired outcome? I'm new to Stata and this is my first post, so I apologise if I'm posting in the wrong area etc.

Thanks!

[Fei Wang]

Kai, using -dataex- to post example data would help. Below I created an example data for your question. Not sure how year and month variables look like in your data -- if different from the example, code needs modification.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(year month price)
2018  1 35
2018  2 27
2018  3 14
2018  4  3
2018  5 87
2018  6 35
2018  7  7
2018  8 32
2018  9 56
2018 10 88
2018 11 20
2018 12 89
2019  1 58
2019  2 37
2019  3 85
2019  4 39
2019  5 12
2019  6 75
2019  7 70
2019  8 69
2019  9 93
2019 10 45
2019 11  7
2019 12 34
2020  1 97
2020  2 73
2020  3  5
2020  4 75
2020  5 50
2020  6 72
2020  7 86
2020  8 13
2020  9 49
2020 10 87
2020 11 77
2020 12 25
end

bys year (month): gen percent = (price[_n] - price[1]) / price[1] * 100

[William Lisowski]

Welcome to Statalist, and to Stata.

Perhaps this example will start you in a useful direction.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mdate price)
708 101.71948
709 101.26815
710 102.97688
711 104.74244
712 105.53294
713  105.5025
714 106.33273
715  108.4876
716 109.96102
717 110.45915
718  111.8022
719 111.52873
720  113.7713
721 114.76422
722 115.49786
723 116.83925
724  117.3869
725  117.4308
726 119.65718
727 119.08843
728 121.72607
729  121.7052
730 123.54408
731 124.17224
732 124.64555
733 125.34586
734 127.61073
735   127.612
736  128.4382
737 130.44946
end
format %tm mdate
generate reference = price
replace reference = reference[_n-1] if month(dofm(mdate))>1
gen price_change = (price - reference)/reference
list, clean noobs

. list, clean noobs

      mdate      price   refere~e   price_~e  
     2019m1   101.7195   101.7195          0  
     2019m2   101.2682   101.7195   -.004437  
     2019m3   102.9769   101.7195   .0123615  
     2019m4   104.7424   101.7195   .0297186  
     2019m5   105.5329   101.7195     .03749  
     2019m6   105.5025   101.7195   .0371907  
     2019m7   106.3327   101.7195   .0453527  
     2019m8   108.4876   101.7195   .0665371  
     2019m9    109.961   101.7195   .0810222  
    2019m10   110.4592   101.7195   .0859193  
    2019m11   111.8022   101.7195   .0991228  
    2019m12   111.5287   101.7195   .0964343  
     2020m1   113.7713   113.7713          0  
     2020m2   114.7642   113.7713   .0087273  
     2020m3   115.4979   113.7713   .0151757  
     2020m4   116.8392   113.7713   .0269659  
     2020m5   117.3869   113.7713   .0317796  
     2020m6   117.4308   113.7713   .0321654  
     2020m7   119.6572   113.7713   .0517343  
     2020m8   119.0884   113.7713   .0467353  
     2020m9   121.7261   113.7713   .0699189  
    2020m10   121.7052   113.7713   .0697355  
    2020m11   123.5441   113.7713   .0858985  
    2020m12   124.1722   113.7713   .0914197  
     2021m1   124.6456   124.6456          0  
     2021m2   125.3459   124.6456   .0056184  
     2021m3   127.6107   124.6456   .0237889  
     2021m4    127.612   124.6456   .0237991  
     2021m5   128.4382   124.6456   .0304275  
     2021m6   130.4495   124.6456   .0465633

[Kai Penfold]

Amazing, that's helped me a lot and I've managed to figure out because of that! Thank you very much Fei and William!