Generate surrounding observations based on observations of another variable

[Sam Volpe]

Hi there,

I am trying to create new observations of a variable based on the observations of other variables. Basically I want to surround the non-missing observations with other observations based on their proximity to said non-missings. I identify growth breaks and categorize them, but the break dates are only single country-year obs and the category is more of a phase.

I was hoping someone could suggest some code for how to get the 5 missing observations before each non-missing obs in grpat2 and the 5 after to equal year in the corresponding row.

So far,
I generate a non-missing value in grpat2 whenever growth_pattern = 1 or 3

gen grpat2 = .
replace grpat2 = year if growth_pattern == 1
replace grpat2 = year if growth_pattern == 3
and I would appreciate help recording the 5 surrounding observations in each direction to also be equal to the year.

Let me know if I can clarify anything.
Thank you,
Sam

* Example generated by -dataex-. For more info, type help dataex
clear
input str52 country_name double year float(b1 b2 b3 b4 growth_regime p0_regime p1_regime p2_regime p3_regime p4_regime pr_diff growth_pattern grpat2)
"Angola"     1981 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1982 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1983 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1984 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1985 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1986 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1987 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1988 1988 1994 2008 . 3 3 0 0 0 0  0 2    .
"Angola"     1989 1988 1994 2008 . 4 0 4 0 0 0  0 2    .
"Angola"     1990 1988 1994 2008 . 4 0 4 0 0 0  0 2    .
"Angola"     1991 1988 1994 2008 . 4 0 4 0 0 0  0 2    .
"Angola"     1992 1988 1994 2008 . 4 0 4 0 0 0  0 2    .
"Angola"     1993 1988 1994 2008 . 4 0 4 0 0 0  0 2    .
"Angola"     1994 1988 1994 2008 . 4 0 4 0 0 0 -3 1 1994
"Angola"     1995 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     1996 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     1997 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     1998 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     1999 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2000 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2001 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2002 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2003 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2004 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2005 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2006 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2007 1988 1994 2008 . 1 0 0 1 0 0  0 2    .
"Angola"     2008 1988 1994 2008 . 1 0 0 1 0 0  3 3 2008
"Angola"     2009 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2010 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2011 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2012 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2013 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2014 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2015 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2016 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2017 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2018 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Angola"     2019 1988 1994 2008 . 4 0 0 0 4 0  0 2    .
"Azerbaijan" 1991 1996 2009    . . 4 4 0 0 0 0  0 2    .
"Azerbaijan" 1992 1996 2009    . . 4 4 0 0 0 0  0 2    .
"Azerbaijan" 1993 1996 2009    . . 4 4 0 0 0 0  0 2    .
"Azerbaijan" 1994 1996 2009    . . 4 4 0 0 0 0  0 2    .
"Azerbaijan" 1995 1996 2009    . . 4 4 0 0 0 0  0 2    .
"Azerbaijan" 1996 1996 2009    . . 4 4 0 0 0 0 -3 1 1996
"Azerbaijan" 1997 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 1998 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 1999 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2000 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2001 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2002 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2003 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2004 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2005 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2006 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2007 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2008 1996 2009    . . 1 0 1 0 0 0  0 2    .
"Azerbaijan" 2009 1996 2009    . . 1 0 1 0 0 0  2 3 2009
"Azerbaijan" 2010 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2011 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2012 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2013 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2014 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2015 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2016 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2017 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2018 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Azerbaijan" 2019 1996 2009    . . 3 0 0 3 0 0  0 2    .
"Bahrain"    1981 1986 1993 2000 . 4 4 0 0 0 0  0 2    .
"Bahrain"    1982 1986 1993 2000 . 4 4 0 0 0 0  0 2    .
"Bahrain"    1983 1986 1993 2000 . 4 4 0 0 0 0  0 2    .
"Bahrain"    1984 1986 1993 2000 . 4 4 0 0 0 0  0 2    .
"Bahrain"    1985 1986 1993 2000 . 4 4 0 0 0 0  0 2    .
"Bahrain"    1986 1986 1993 2000 . 4 4 0 0 0 0 -2 1 1986
"Bahrain"    1987 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1988 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1989 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1990 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1991 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1992 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1993 1986 1993 2000 . 2 0 2 0 0 0  0 2    .
"Bahrain"    1994 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    1995 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    1996 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    1997 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    1998 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    1999 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    2000 1986 1993 2000 . 3 0 0 3 0 0  0 2    .
"Bahrain"    2001 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2002 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2003 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2004 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2005 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2006 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2007 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2008 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2009 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2010 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2011 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
"Bahrain"    2012 1986 1993 2000 . 3 0 0 0 3 0  0 2    .
end
label values growth_regime gr_regimes_labels
label values p0_regime gr_regimes_labels
label values p1_regime gr_regimes_labels
label values p2_regime gr_regimes_labels
label values p3_regime gr_regimes_labels
label values p4_regime gr_regimes_labels
label def gr_regimes_labels 1 "Crisis", modify
label def gr_regimes_labels 2 "Stagnant", modify
label def gr_regimes_labels 3 "Stable", modify
label def gr_regimes_labels 4 "Miracle", modify
label values growth_pattern gr_pattern_labels
label def gr_pattern_labels 1 "Acceleration", modify
label def gr_pattern_labels 2 "Maintenance", modify
label def gr_pattern_labels 3 "Decline", modify

[Ken Chui]

rangestat (mean) grpat2, interval(year -5 0) by(country_name)

Also, the three-line code can become one:

gen grpat2 = year if inlist(growth_pattern, 1, 3)

[Sam Volpe]

Hi Ken Chui
Thank you very much, also for your quick response!

I just gave that a shot and it copies the number from grpat2 rather than from year. For an example, after 1994, I'd like the 5 folllowing obs to be 1995, 1996, 1997, 1998, 1999.

Do you have any follow up suggestions?
No worries if not,
Sam

* Example generated by -dataex-. For more info, type help dataex
clear
input str52 country_name double year float grpat2 double grpat2_mean
"Angola"     1981    .    .
"Angola"     1982    .    .
"Angola"     1983    .    .
"Angola"     1984    .    .
"Angola"     1985    .    .
"Angola"     1986    .    .
"Angola"     1987    .    .
"Angola"     1988    .    .
"Angola"     1989    .    .
"Angola"     1990    .    .
"Angola"     1991    .    .
"Angola"     1992    .    .
"Angola"     1993    .    .
"Angola"     1994 1994 1994
"Angola"     1995    . 1994
"Angola"     1996    . 1994
"Angola"     1997    . 1994
"Angola"     1998    . 1994
"Angola"     1999    . 1994
"Angola"     2000    .    .
"Angola"     2001    .    .
"Angola"     2002    .    .
"Angola"     2003    .    .
"Angola"     2004    .    .
"Angola"     2005    .    .
"Angola"     2006    .    .
"Angola"     2007    .    .
"Angola"     2008 2008 2008
"Angola"     2009    . 2008
"Angola"     2010    . 2008
"Angola"     2011    . 2008
"Angola"     2012    . 2008
"Angola"     2013    . 2008
"Angola"     2014    .    .
"Angola"     2015    .    .
"Angola"     2016    .    .
"Angola"     2017    .    .
"Angola"     2018    .    .
"Angola"     2019    .    .
"Azerbaijan" 1991    .    .
"Azerbaijan" 1992    .    .
"Azerbaijan" 1993    .    .
"Azerbaijan" 1994    .    .
"Azerbaijan" 1995    .    .
"Azerbaijan" 1996 1996 1996
"Azerbaijan" 1997    . 1996
"Azerbaijan" 1998    . 1996
"Azerbaijan" 1999    . 1996
"Azerbaijan" 2000    . 1996
"Azerbaijan" 2001    . 1996
"Azerbaijan" 2002    .    .
"Azerbaijan" 2003    .    .
"Azerbaijan" 2004    .    .
"Azerbaijan" 2005    .    .
"Azerbaijan" 2006    .    .
"Azerbaijan" 2007    .    .
"Azerbaijan" 2008    .    .
"Azerbaijan" 2009 2009 2009
"Azerbaijan" 2010    . 2009
"Azerbaijan" 2011    . 2009
"Azerbaijan" 2012    . 2009
"Azerbaijan" 2013    . 2009
"Azerbaijan" 2014    . 2009
"Azerbaijan" 2015    .    .
"Azerbaijan" 2016    .    .
"Azerbaijan" 2017    .    .
"Azerbaijan" 2018    .    .
"Azerbaijan" 2019    .    .
"Bahrain"    1981    .    .
"Bahrain"    1982    .    .
"Bahrain"    1983    .    .
"Bahrain"    1984    .    .
"Bahrain"    1985    .    .
"Bahrain"    1986 1986 1986
"Bahrain"    1987    . 1986
"Bahrain"    1988    . 1986
"Bahrain"    1989    . 1986
"Bahrain"    1990    . 1986
"Bahrain"    1991    . 1986
"Bahrain"    1992    .    .
"Bahrain"    1993    .    .
"Bahrain"    1994    .    .
"Bahrain"    1995    .    .
"Bahrain"    1996    .    .
"Bahrain"    1997    .    .
"Bahrain"    1998    .    .
"Bahrain"    1999    .    .
"Bahrain"    2000    .    .
"Bahrain"    2001    .    .
"Bahrain"    2002    .    .
"Bahrain"    2003    .    .
"Bahrain"    2004    .    .
"Bahrain"    2005    .    .
"Bahrain"    2006    .    .
"Bahrain"    2007    .    .
"Bahrain"    2008    .    .
"Bahrain"    2009    .    .
"Bahrain"    2010    .    .
"Bahrain"    2011    .    .
"Bahrain"    2012    .    .
end

[Ken Chui]

Hi Ken Chui
Thank you very much, also for your quick response!

I just gave that a shot and it copies the number from grpat2 rather than from year. For an example, after 1994, I'd like the 5 folllowing obs to be 1995, 1996, 1997, 1998, 1999.

Do you have any follow up suggestions?
No worries if not,
Sam

replace grpat2_mean = year if !missing(grpat2_mean)

[Sam Volpe]

Ken Chui Bless you, that's wonderful! I feel a bit silly for not thinking of it myself, I suppose I was overthinking/panicking a bit.