Copying / Filling in a column using the last value based on a specific condition

[cjgodlewski]

Hello

(i wasn't sure what kind of title i could write, not sure i did it right)

for each country, i need to replace eventyear by the year when Mapped_Poltightening>0
for instance i need to copy/fill in 1996 for Austria several times in the column eventyear until eventyear = 2003, then i need to copy/fill in the eventyear column with 2003 until 2008, then again with 2008 until 2010 and so on
i hope i was clear enough...
many thanks for all your kind help

CG

* Example generated by -dataex-. For more info, type help dataex
clear
input str14 country str4 isocode3 int InforcesinceYear byte(Mapped_Poltightening Mapped_Polloosening Mapped_PolOtherambiguousimpact _fillin) float eventyear
"Austria"  "AUT" 1996 1 0 0 0 1996
"Austria"  "AUT" 1997 0 0 0 1    .
"Austria"  "AUT" 1998 0 0 0 1    .
"Austria"  "AUT" 1999 0 0 0 1    .
"Austria"  "AUT" 2000 0 0 0 1    .
"Austria"  "AUT" 2001 0 0 0 1    .
"Austria"  "AUT" 2002 0 0 0 1    .
"Austria"  "AUT" 2003 1 0 0 0 2003
"Austria"  "AUT" 2004 0 0 0 1    .
"Austria"  "AUT" 2005 0 0 0 1    .
"Austria"  "AUT" 2006 0 0 1 0    .
"Austria"  "AUT" 2007 0 0 0 1    .
"Austria"  "AUT" 2008 1 0 0 0 2008
"Austria"  "AUT" 2009 0 0 0 1    .
"Austria"  "AUT" 2010 1 0 0 0 2010
"Austria"  "AUT" 2011 1 0 0 0 2011
"Austria"  "AUT" 2012 1 0 0 0 2012
"Austria"  "AUT" 2013 1 0 0 0 2013
"Austria"  "AUT" 2014 0 0 0 1    .
"Austria"  "AUT" 2015 1 0 0 0 2015
"Austria"  "AUT" 2016 1 0 0 0 2016
"Austria"  "AUT" 2017 1 0 0 0 2017
"Austria"  "AUT" 2018 1 0 0 0 2018
"Belgium"  "BEL" 1996 0 0 0 1    .
"Belgium"  "BEL" 1997 0 0 0 1    .
"Belgium"  "BEL" 1998 0 0 0 1    .
"Belgium"  "BEL" 1999 0 0 0 1    .
"Belgium"  "BEL" 2000 0 0 0 1    .
"Belgium"  "BEL" 2001 0 0 0 1    .
"Belgium"  "BEL" 2002 0 0 0 1    .
"Belgium"  "BEL" 2003 0 0 0 1    .
"Belgium"  "BEL" 2004 0 0 0 1    .
"Belgium"  "BEL" 2005 0 0 1 0    .
"Belgium"  "BEL" 2006 0 0 1 0    .
"Belgium"  "BEL" 2007 0 0 0 1    .
"Belgium"  "BEL" 2008 0 0 0 1    .
"Belgium"  "BEL" 2009 0 0 0 1    .
"Belgium"  "BEL" 2010 0 0 0 1    .
"Belgium"  "BEL" 2011 1 0 0 0 2011
"Belgium"  "BEL" 2012 1 0 0 0 2012
"Belgium"  "BEL" 2013 0 0 0 1    .
"Belgium"  "BEL" 2014 0 0 1 0    .
"Belgium"  "BEL" 2015 0 0 0 1    .
"Belgium"  "BEL" 2016 0 0 0 1    .
"Belgium"  "BEL" 2017 0 0 0 1    .
"Belgium"  "BEL" 2018 0 0 0 1    .
"Bulgaria" "BGR" 1996 0 0 0 1    .
"Bulgaria" "BGR" 1997 5 0 0 0 1997
"Bulgaria" "BGR" 1998 0 0 0 1    .
"Bulgaria" "BGR" 1999 0 2 0 0    .
"Bulgaria" "BGR" 2000 0 0 0 1    .
"Bulgaria" "BGR" 2001 0 0 0 1    .
"Bulgaria" "BGR" 2002 0 0 0 1    .
"Bulgaria" "BGR" 2003 0 2 0 0    .
"Bulgaria" "BGR" 2004 4 0 1 0 2004
"Bulgaria" "BGR" 2005 3 0 1 0 2005
"Bulgaria" "BGR" 2006 3 2 0 0 2006
"Bulgaria" "BGR" 2007 6 1 0 0 2007
"Bulgaria" "BGR" 2008 1 2 0 0 2008
"Bulgaria" "BGR" 2009 2 3 0 0 2009
"Bulgaria" "BGR" 2010 2 0 0 0 2010
"Bulgaria" "BGR" 2011 0 0 0 1    .
"Bulgaria" "BGR" 2012 0 0 0 1    .
"Bulgaria" "BGR" 2013 0 0 0 1    .
"Bulgaria" "BGR" 2014 1 0 1 0 2014
"Bulgaria" "BGR" 2015 0 0 0 1    .
"Bulgaria" "BGR" 2016 0 0 0 1    .
"Bulgaria" "BGR" 2017 0 0 0 1    .
"Bulgaria" "BGR" 2018 0 0 0 1    .
"Croatia"  "HRV" 1996 1 2 0 0 1996
"Croatia"  "HRV" 1997 0 0 0 1    .
"Croatia"  "HRV" 1998 0 0 0 1    .
"Croatia"  "HRV" 1999 0 0 0 1    .
"Croatia"  "HRV" 2000 0 2 0 0    .
"Croatia"  "HRV" 2001 3 2 0 0 2001
"Croatia"  "HRV" 2002 0 0 0 1    .
"Croatia"  "HRV" 2003 6 2 0 0 2003
"Croatia"  "HRV" 2004 3 1 0 0 2004
"Croatia"  "HRV" 2005 3 1 0 0 2005
"Croatia"  "HRV" 2006 7 1 0 0 2006
"Croatia"  "HRV" 2007 6 0 0 0 2007
"Croatia"  "HRV" 2008 2 3 0 0 2008
"Croatia"  "HRV" 2009 1 5 0 0 2009
"Croatia"  "HRV" 2010 1 1 0 0 2010
"Croatia"  "HRV" 2011 1 1 0 0 2011
"Croatia"  "HRV" 2012 1 2 0 0 2012
"Croatia"  "HRV" 2013 0 2 0 0    .
"Croatia"  "HRV" 2014 2 3 0 0 2014
"Croatia"  "HRV" 2015 0 0 1 0    .
"Croatia"  "HRV" 2016 1 0 0 0 2016
"Croatia"  "HRV" 2017 0 0 0 1    .
"Croatia"  "HRV" 2018 0 0 0 1    .
"Cyprus"   "CYP" 1996 0 0 0 1    .
"Cyprus"   "CYP" 1997 0 0 0 1    .
"Cyprus"   "CYP" 1998 0 0 0 1    .
"Cyprus"   "CYP" 1999 0 0 0 1    .
"Cyprus"   "CYP" 2000 0 0 0 1    .
"Cyprus"   "CYP" 2001 0 0 0 1    .
"Cyprus"   "CYP" 2002 0 0 0 1    .
"Cyprus"   "CYP" 2003 2 0 0 0 2003
end

[Andrew Musau]

bys country (InforcesinceYear): replace eventyear=eventyear[_n-1] if missing(eventyear) & !missing(eventyear[_n-1])

[cjgodlewski]

it always look so simple ex post...
many thanks Andrew !

[Nick Cox]

See also https://www.stata.com/support/faqs/d...issing-values/

[cjgodlewski]

Thank you Nick !