How to multiply all observations in previous periods within one variable in panel data?

[Linghui Han]

I want to convert the cpi with previous year as the base year to cpi with year 1996 as the base year. How can I achieve this? Here is the data. Thanks!

* Example generated by -dataex-. To install: ssc install dataex
clear
input double id float year double cpi
11 1997              1.053
11 1998              1.024
11 1999              1.006
11 2000              1.035
11 2001              1.031
11 2002               .982
11 2003              1.002
11 2004               1.01
11 2005              1.015
11 2006 1.0090000000000001
11 2007              1.024
11 2008              1.051
11 2009               .985
12 1997              1.031
12 1998               .995
12 1999  .9890000000000001
12 2000               .996
12 2001              1.012
12 2002               .996
12 2003               1.01
12 2004              1.023
12 2005              1.015
12 2006              1.015
12 2007              1.042
12 2008              1.054
12 2009                .99
13 1997              1.035
13 1998  .9840000000000001
13 1999               .981
13 2000               .997
13 2001              1.005
13 2002                .99
13 2003              1.022
13 2004              1.043
13 2005              1.018
13 2006 1.0170000000000001
13 2007              1.047
13 2008              1.062
13 2009               .993
14 1997              1.031
14 1998               .986
14 1999               .996
14 2000 1.0390000000000001
14 2001               .998
14 2002  .9840000000000001
14 2003              1.018
14 2004              1.041
14 2005              1.023
14 2006               1.02
14 2007              1.046
14 2008              1.072
14 2009               .996
15 1997              1.045
15 1998               .993
15 1999               .998
15 2000              1.013
15 2001              1.006
15 2002              1.002
15 2003              1.022
15 2004 1.0290000000000001
15 2005              1.024
15 2006              1.015
15 2007              1.046
15 2008              1.057
15 2009               .997
21 1997              1.031
21 1998               .993
21 1999               .986
21 2000  .9990000000000001
21 2001                  1
21 2002  .9890000000000001
21 2003 1.0170000000000001
21 2004              1.035
21 2005              1.014
21 2006              1.012
21 2007              1.051
21 2008              1.046
21 2009                  1
22 1997              1.037
22 1998               .992
22 1999                .98
22 2000               .986
22 2001              1.013
22 2002               .995
22 2003              1.012
22 2004              1.041
22 2005              1.015
22 2006              1.014
22 2007              1.053
22 2008              1.051
22 2009              1.001
23 1997              1.044
23 1998              1.004
23 1999               .968
23 2000               .983
23 2001              1.008
23 2002               .993
23 2003 1.0090000000000001
23 2004              1.038
23 2005              1.012
end
label values id id
label def id 11 "Beijing", modify
label def id 12 "Tianjin", modify
label def id 13 "Hebei", modify
label def id 14 "Shanxi", modify
label def id 15 "Innermongolia", modify
label def id 21 "Liaoning", modify
label def id 22 "Jilin", modify
label def id 23 "Heilongjiang", modify

[Clyde Schechter]

by id (year), sort: assert year == 1996 + _n
by id (year): gen double cpi_base_1996 = cpi if _n == 1
by id (year): replace cpi_base_1996 = cpi_base_1996[_n-1] * cpi if _n > 1

[Linghui Han]

Thank you very much! Using "by id" instead of "by id (year)" only multiplies the previous and the current observations. This makes the difference?

[Clyde Schechter]

The results of that code with -by id- replacing -by id (year)- are actually unpredictable and irreproducible. The reason is that if you sort on id without also sorting by year within id, the observations within id will be sorted into some random order, not chronological order. That is a property of the way Stata does sorting: if the sort variables do not uniquely identify observations, everything else gets put into random order. So without specifying that the data should be sorted by year within id, the subsequent multiplication will mutiply the current observation with the observation from whatever random year happened to get sorted before it in the data set.

[Linghui Han]

Just check this forum again. Got your point. Thank you so much! Wish you a great holiday!