Replacing observations in a variable with the value other observations using a loop

[Uzy Nduka]

Hello Everyone.

I have a panel dataset with price indexes for different sectors and countries, across years. So it is a 3 dimensional panel which I can easily make a two dimensional panel by grouping. The issue is some sectors are broad 2-digit classifications eg 10, while some have 4-digit classifications eg 1011 or 3-digit classifications eg 103. I want to replace the price index of a 2-digit or 3-digit sector with the price index of a 4-digit sector by country. So for instance, if price index of sector 10 = 100, price index of sector 103 = 100.1 and price index of sector 1011 = 105 for Mexico, then I would like all the indexes to be set to 105 for Mexico. I have four variables that I would like to do this replacement for based on the sector codes and the only condition is that the first two numbers must be a match. So for instance, I can assign the values of sector 10 to sector 1011, but not the values of sector 11 to 1011.
Any help I can get is appreciated.

[Andrew Musau]

Much easier to answer this kind of question if you provide a data example using dataex (see FAQ 12.2). You can start by creating a two digit sector identifier and then specify a condition based on that. The following assumes that all your variables are numerical and that your data is in long format. Additionally, the largest sector identifier is 4 digits. With multiple 4 digit sectors in a country, the price index value will be assigned corresponding to the largest value of sector.

gen s2id = real(substr(string(sector), 1, 2))
bys country s2id (sector): replace price_index= price_index[_N]

[Uzy Nduka]

Hello Andrew,
Thank you for your input. Please see a snapshot of the data

input str53 country int year str4 nacerev2 double total_output_price_index2015 float s2id
"SE - Sweden" 1991 "25" "51.8"              25
"SE - Sweden" 1992 "25" "52"                25
"SE - Sweden" 1993 "25" "53.8"              25
"SE - Sweden" 1994 "25" "56"                25
"SE - Sweden" 1995 "25" "59.7"              25
"SE - Sweden" 1996 "25" "61.1"              25
"SE - Sweden" 1997 "25" "61.8"              25
"SE - Sweden" 1998 "25" "63.5"              25
"SE - Sweden" 1999 "25" "64.8"              25
"SE - Sweden" 2000 "25" "66.59999999999999" 25
"SE - Sweden" 2001 "25" "69.09999999999999" 25
"SE - Sweden" 2002 "25" "71.8"              25
"SE - Sweden" 2003 "25" "73"                25
"SE - Sweden" 2004 "25" "76.3"              25
"SE - Sweden" 2005 "25" "81.8"              25
"SE - Sweden" 2006 "25" "84.40000000000001" 25
"SE - Sweden" 2007 "25" "87.8"              25
"SE - Sweden" 2008 "25" "92.3"              25
"SE - Sweden" 2009 "25" "96.40000000000001" 25
"SE - Sweden" 2010 "25" "95"                25
"SE - Sweden" 2011 "25" "96.59999999999999" 25
"SE - Sweden" 2012 "25" "98.5"              25
"SE - Sweden" 2013 "25" "98.3"              25
"SE - Sweden" 2014 "25" "98.90000000000001" 25
"SE - Sweden" 2015 "25" "100"               25
"SE - Sweden" 2016 "25" "100.4"             25
"SE - Sweden" 2017 "25" "104.4"             25
"SE - Sweden" 2018 "25" "109"               25
"SE - Sweden" 1991 "251" "49.5"              25
"SE - Sweden" 1992 "251" "47.5"              25
"SE - Sweden" 1993 "251" "49.3"              25
"SE - Sweden" 1994 "251" "51.8"              25
"SE - Sweden" 1995 "251" "56.9"              25
"SE - Sweden" 1996 "251" "56.7"              25
"SE - Sweden" 1997 "251" "57.3"              25
"SE - Sweden" 1998 "251" "61.2"              25
"SE - Sweden" 1999 "251" "62.3"              25
"SE - Sweden" 2000 "251" "63.9"              25
"SE - Sweden" 2001 "251" "65.5"              25
"SE - Sweden" 2002 "251" "68.8"              25
"SE - Sweden" 2003 "251" "70.5"              25
"SE - Sweden" 2004 "251" "76.3"              25
"SE - Sweden" 2005 "251" "82.40000000000001" 25
"SE - Sweden" 2006 "251" "85.2"              25
"SE - Sweden" 2007 "251" "89.3"              25
"SE - Sweden" 2008 "251" "95.7"              25
"SE - Sweden" 2009 "251" "96.40000000000001" 25
"SE - Sweden" 2010 "251" "95.8"              25
"SE - Sweden" 2011 "251" "98.90000000000001" 25
"SE - Sweden" 2012 "251" "98.90000000000001" 25
"SE - Sweden" 2013 "251" "100.4"             25
"SE - Sweden" 2014 "251" "100"               25
"SE - Sweden" 2015 "251" "100"               25
"SE - Sweden" 2016 "251" "99.59999999999999" 25
"SE - Sweden" 2017 "251" "104"               25
"SE - Sweden" 2018 "251" "110.7"             25
"SE - Sweden" 1991 "2511" "48.6"              25
"SE - Sweden" 1992 "2511" "46.2"              25
"SE - Sweden" 1993 "2511" "48.3"              25
"SE - Sweden" 1994 "2511" "51.1"              25
"SE - Sweden" 1995 "2511" "56.1"              25
"SE - Sweden" 1996 "2511" "55.4"              25
"SE - Sweden" 1997 "2511" "56.2"              25
"SE - Sweden" 1998 "2511" "61.2"              25
"SE - Sweden" 1999 "2511" "62.5"              25
"SE - Sweden" 2000 "2511" "64.09999999999999" 25
"SE - Sweden" 2001 "2511" "65.5"              25
"SE - Sweden" 2002 "2511" "69.3"              25
"SE - Sweden" 2003 "2511" "71.3"              25
"SE - Sweden" 2004 "2511" "78.40000000000001" 25
"SE - Sweden" 2005 "2511" "85.5"              25
"SE - Sweden" 2006 "2511" "87.7"              25
"SE - Sweden" 2007 "2511" "91.7"              25
"SE - Sweden" 2008 "2511" "98.2"              25
"SE - Sweden" 2009 "2511" "98.5"              25
"SE - Sweden" 2010 "2511" "97.8"              25
"SE - Sweden" 2011 "2511" "101.1"             25
"SE - Sweden" 2012 "2511" "100.5"             25
"SE - Sweden" 2013 "2511" "101.7"             25
"SE - Sweden" 2014 "2511" "100.6"             25
"SE - Sweden" 2015 "2511" "100"               25
"SE - Sweden" 2016 "2511" "100"               25
"SE - Sweden" 2017 "2511" "104.6"             25
"SE - Sweden" 2018 "2511" "111.2"             25

end

In this instance, I would like the indexes for nace code 25 and 251 set to the index of 2511.

For your first code, my sectors were still in string in order to retain the leading zeros, so I tweaked your code and used the code below


gen s2id = real(substr(nacerev2), 1, 2)

However, please note that I have some of the indexes missing so I tried this


bys country s2id (nacerev2): replace total_output_price_index2010 = total_output_price_index2010[_N] if total_output_price_index2010 !=.

But this didn't work.

Is there anything else I need to do?

Thanks again

[Andrew Musau]

You can define your sample beforehand. But you need to do this for each index. Your index is defined for each year, so the value will take the value of the index in the final sample year (2018 in your data example). Of course, you can change this if needed.

gen touse=!missing(total_output_price_index2015)
bys country s2id touse (nacerev2): replace total_output_price_index2015 = total_output_price_index2015[_N] if touse

[Uzy Nduka]

This worked! Thank you so much for your help Andrew