Indexing and rebasing a series

[Ernestina delPiero]

Hi,

I am looking for advice on the following.

I have a dataset where each series is currently 'chained', meaning that the base period for each time period is the immediately preceding time period. I need to revert these chained series to have a fixed base, e.g. to one where a given time period = 100. The formula to convert a chain base to fixed base is: current year's fixed-index = (current year's chain-index * previous year's fixed-index) / 100. An example of the formula is shown here.

I figured out how to do this in two steps in Excel, but cannot implement it in Stata. The two steps:
1. Make the year 2005 always equal to 100 (this is needed to start and extend the formula above)
2. Apply the formula above
3. Rebase the fixed index series from 2005 to 2011

Here is an example of my current (chained) data set:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte Code str1(Name Country) double(y2005 y2006 y2007 y2008 y2009 y2010 y2011 y2012 y2013 y2014)
1 "X" "A" 100.64 102.89 104.41 108.95 103.41 108.19 107.65 102.56 105.79 102.98
2 "Y" "A"  100.8  103.3  106.7  111.1   99.1  109.4  112.3    102  105.8  101.7
end

And here is what it looks after converting it to fixed base and rebasing it in Excel:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte Code str1(Name Country) double(y2005 y2006 y2007 y2008 y2009 y2010) byte y2011 double(y2012 y2013 y2014)
1 "X" "A" 70.94042881827724 72.99060721112545 76.20949298913608 83.03024261166377 85.86157388472148 92.89363678588018 100 102.56 108.49822400000002 111.73147107520003
2 "Y" "A" 67.07342251233523 69.28684545524229 73.92906410074353 82.13519021592606 81.39597350398272 89.04719501335708 100    102 107.91600000000001 109.75057200000002
end

Any tips to implement this in Stata, would be helpful.

[Clyde Schechter]

So, as with most things in Stata, this is very simple in long layout and very difficult in wide. And, as is also often the case in Stata, thinking about the way you would do it in Excel is not particularly helpful.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte Code str1(Name Country) double(y2005 y2006 y2007 y2008 y2009 y2010 y2011 y2012 y2013 y2014)
1 "X" "A" 100.64 102.89 104.41 108.95 103.41 108.19 107.65 102.56 105.79 102.98
2 "Y" "A"  100.8  103.3  106.7  111.1   99.1  109.4  112.3    102  105.8  101.7
end

//    GO TO LONG LAYOUT
gen long obs_no = _n
reshape long y, i(obs_no) j(year)
rename y chain_based_index

//    CALCULATE FIXED INDEX
gen double fixed_index_2005 = 100 if year == 2005
by obs_no (year), sort: replace fixed_index_2005 = ///
    fixed_index_2005[_n-1]*chain_based_index/100 if _n > 1
//    AND REBASE THE INDEX TO 2011
by obs_no: egen double index_value_2011 = max(cond(year == 2011, fixed_index_2005, .))
gen double fixed_index_2011 = 100*fixed_index_2005/index_value_2011

Whatever you plan to do next with this data will probably also be easier in long layout, so I'm leaving it that way. If there is a compelling reason to go back to wide layout, then you can use -reshape- to do that. See -help reshape-.

By the way, I created the obs_no variable so as to guarantee that there was a unique identifier of each observation to use in the -i()- option of -reshape-. But if Code Name and Country jointly uniquely identify observations, then you don't need that step and you can just use -i(Code Name Country)- instead. Within your example, Code Name and Country do serve in this role, but I didn't want to presume that was true throughout your data.