How to replace the values of a string variable in one year with the value of another year for the same unit?

[Linghui Han]

Hi,

I'd like to have all countries have their geo code in every year for the following dataset. Many thanks!

* Example generated by -dataex-. To install: ssc install dataex
clear
input str44 country int year str3 geo
"Afghanistan, Islamic Rep. of" 1990 ""   
"Afghanistan, Islamic Rep. of" 1991 ""   
"Afghanistan, Islamic Rep. of" 1992 ""   
"Afghanistan, Islamic Rep. of" 1993 ""   
"Afghanistan, Islamic Rep. of" 1994 ""   
"Afghanistan, Islamic Rep. of" 1995 ""   
"Afghanistan, Islamic Rep. of" 1996 ""   
"Afghanistan, Islamic Rep. of" 1997 ""   
"Afghanistan, Islamic Rep. of" 1998 ""   
"Afghanistan, Islamic Rep. of" 1999 ""   
"Afghanistan, Islamic Rep. of" 2000 ""   
"Afghanistan, Islamic Rep. of" 2001 ""   
"Afghanistan, Islamic Rep. of" 2002 ""   
"Afghanistan, Islamic Rep. of" 2003 ""   
"Afghanistan, Islamic Rep. of" 2004 ""   
"Afghanistan, Islamic Rep. of" 2005 ""   
"Afghanistan, Islamic Rep. of" 2006 "afg"
"Afghanistan, Islamic Rep. of" 2007 "afg"
"Afghanistan, Islamic Rep. of" 2008 "afg"
"Afghanistan, Islamic Rep. of" 2009 "afg"
"Afghanistan, Islamic Rep. of" 2010 "afg"
"Afghanistan, Islamic Rep. of" 2011 "afg"
"Afghanistan, Islamic Rep. of" 2012 "afg"
"Afghanistan, Islamic Rep. of" 2013 "afg"
"Afghanistan, Islamic Rep. of" 2014 "afg"
"Afghanistan, Islamic Rep. of" 2015 "afg"
"Afghanistan, Islamic Rep. of" 2016 "afg"
"Afghanistan, Islamic Rep. of" 2017 "afg"
"Afghanistan, Islamic Rep. of" 2018 "afg"
"Afghanistan, Islamic Rep. of" 2019 "afg"
"Albania"                      1990 ""   
"Albania"                      1991 ""   
"Albania"                      1992 ""   
"Albania"                      1993 ""   
"Albania"                      1994 ""   
"Albania"                      1995 ""   
"Albania"                      1996 ""   
"Albania"                      1997 ""   
"Albania"                      1998 ""   
"Albania"                      1999 ""   
"Albania"                      2000 ""   
"Albania"                      2001 ""   
"Albania"                      2002 ""   
"Albania"                      2003 ""   
"Albania"                      2004 ""   
"Albania"                      2005 ""   
"Albania"                      2006 "alb"
"Albania"                      2007 "alb"
"Albania"                      2008 "alb"
"Albania"                      2009 "alb"
"Albania"                      2010 "alb"
"Albania"                      2011 "alb"
"Albania"                      2012 "alb"
"Albania"                      2013 "alb"
"Albania"                      2014 "alb"
"Albania"                      2015 "alb"
"Albania"                      2016 "alb"
"Albania"                      2017 "alb"
"Albania"                      2018 "alb"
"Albania"                      2019 "alb"
"Algeria"                      1990 ""   
"Algeria"                      1991 ""   
"Algeria"                      1992 ""   
"Algeria"                      1993 ""   
"Algeria"                      1994 ""   
"Algeria"                      1995 ""   
"Algeria"                      1996 ""   
"Algeria"                      1997 ""   
"Algeria"                      1998 ""   
"Algeria"                      1999 ""   
"Algeria"                      2000 ""   
"Algeria"                      2001 ""   
"Algeria"                      2002 ""   
"Algeria"                      2003 ""   
"Algeria"                      2004 ""   
"Algeria"                      2005 ""   
"Algeria"                      2006 "dza"
"Algeria"                      2007 "dza"
"Algeria"                      2008 "dza"
"Algeria"                      2009 "dza"
"Algeria"                      2010 "dza"
"Algeria"                      2011 "dza"
"Algeria"                      2012 "dza"
"Algeria"                      2013 "dza"
"Algeria"                      2014 "dza"
"Algeria"                      2015 "dza"
"Algeria"                      2016 "dza"
"Algeria"                      2017 "dza"
"Algeria"                      2018 "dza"
"Algeria"                      2019 "dza"
"Angola"                       1990 ""   
"Angola"                       1991 ""   
"Angola"                       1992 ""   
"Angola"                       1993 ""   
"Angola"                       1994 ""   
"Angola"                       1995 ""   
"Angola"                       1996 ""   
"Angola"                       1997 ""   
"Angola"                       1998 ""   
"Angola"                       1999 ""   
end

[Øyvind Snilsberg]

one way to do this,

bysort country (geo): replace geo = geo[_N]

[Linghui Han]

Many thanks! It works.

Linghui

[Linghui Han]

But #2 works on the condition that there is geo in the last year for the unit. How about if there is a value for the variable geo in any year?

[Øyvind Snilsberg]

#2 will work irrespective of which years have non-missing values for geo because it sorts non-missing values for geo last,

* Example generated by -dataex-. For more info, type help dataex
clear
input str28 country int year str3 geo
"Afghanistan, Islamic Rep. of" 2007 ""   
"Afghanistan, Islamic Rep. of" 2008 ""   
"Afghanistan, Islamic Rep. of" 2009 "afg"
"Afghanistan, Islamic Rep. of" 2010 "afg"
"Afghanistan, Islamic Rep. of" 2011 ""   
"Afghanistan, Islamic Rep. of" 2012 ""   
end

list, clean

                            country   year   geo  
  1.   Afghanistan, Islamic Rep. of   2007        
  2.   Afghanistan, Islamic Rep. of   2008        
  3.   Afghanistan, Islamic Rep. of   2009   afg  
  4.   Afghanistan, Islamic Rep. of   2010   afg  
  5.   Afghanistan, Islamic Rep. of   2011        
  6.   Afghanistan, Islamic Rep. of   2012        

bysort country (geo): replace geo = geo[_N]

list, clean

                            country   year   geo  
  1.   Afghanistan, Islamic Rep. of   2007   afg  
  2.   Afghanistan, Islamic Rep. of   2012   afg  
  3.   Afghanistan, Islamic Rep. of   2011   afg  
  4.   Afghanistan, Islamic Rep. of   2008   afg  
  5.   Afghanistan, Islamic Rep. of   2009   afg  
  6.   Afghanistan, Islamic Rep. of   2010   afg

[Linghui Han]

I see. Thank you very much!