Repeat values through multiple observations in irregular dataset

[David Sosa]

Hello everyone,

I'm working with a dataset related to agricultural production that appears to have been designed for printing rather than for statistical analysis. Therefore I'm running into some problems with the variable "v1". This variable contains both the agricultural product and the yearm but I would like to separate them into two different variables: product and year. I have generated the year variable using destring v1, gen(year) force. But I don't know how to generate the product variable.

This is how my dataset looks:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str69 v1 str12(production price)
   . "Agave tequilero"            ""         ""        
2003 "2003"                       "310.00"   "0"       
2004 "2004"                       "370.00"   "0"       
2005 "2005"                       "375.00"   "0"       
2006 "2006"                       "375.00"   "0"       
2008 "2008"                       "375.00"   "0"       
2009 "2009"                       "375.00"   "0"       
2010 "2010"                       "344.00"   "0"       
2011 "2011"                       "344.00"   "0"       
2012 "2012"                       "240.00"   "0"       
2013 "2013"                       "241.00"   "0"       
2014 "2014"                       "71.00"    "0"       
2020 "2020"                       "8.00"     "0"       
2021 "2021"                       "8.00"     "0"       
   . "Agave tequilero weber"      ""         ""        
2007 "2007"                       "374.00"   "0"       
   . "Ajo s/clasificar"           ""         ""        
2012 "2012"                       "9.00"     "9,200.00"
2013 "2013"                       "2.00"     "9,000.00"
   . "Alfalfa verde s/clasificar" ""         ""        
2003 "2003"                       "1,963.00" "336.55"  
2004 "2004"                       "1,945.00" "324.65"  
2005 "2005"                       "2,083.00" "303.81"  
2006 "2006"                       "2,058.00" "364.20"  
2007 "2007"                       "1,913.00" "391.77"  
end

This is how I want it to look:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str69 product str12(production price)
2003 "Agave tequilero"            "310.00"   "0"       
2004 "Agave tequilero"            "370.00"   "0"       
2005 "Agave tequilero"            "375.00"   "0"       
2006 "Agave tequilero"            "375.00"   "0"       
2008 "Agave tequilero"            "375.00"   "0"       
2009 "Agave tequilero"            "375.00"   "0"       
2010 "Agave tequilero"            "344.00"   "0"       
2011 "Agave tequilero"            "344.00"   "0"       
2012 "Agave tequilero"            "240.00"   "0"       
2013 "Agave tequilero"            "241.00"   "0"       
2014 "Agave tequilero"            "71.00"    "0"       
2020 "Agave tequilero"            "8.00"     "0"       
2021 "Agave tequilero"            "8.00"     "0"       
2007 "Agave tequilero weber"      "374.00"   "0"       
2012 "Ajo s/clasificar"           "9.00"     "9,200.00"
2013 "Ajo s/clasificar"           "2.00"     "9,000.00"
2003 "Alfalfa verde s/clasificar" "1,963.00" "336.55"  
2004 "Alfalfa verde s/clasificar" "1,945.00" "324.65"  
2005 "Alfalfa verde s/clasificar" "2,083.00" "303.81"  
2006 "Alfalfa verde s/clasificar" "2,058.00" "364.20"  
2007 "Alfalfa verde s/clasificar" "1,913.00" "391.77"  
end

Thank you in advance!
David Sosa

[William Lisowski]

Perhaps this code will start you in a useful direction.

replace v1 = v1[_n-1] if year!=.
rename v1 product
drop if year==.

[David Sosa]

Thank you William!

That worked perfectly