Dear All,

I have three variables in my dataset, ID, year, and object. The "object" is a categorical variable. I want to create new variables based on different levels of "object", where I want my observations to be the number of occurrences of the given object in a given year for the given ID.

What I did so far is that I generated different levels of the "object" variable with
Code:
tab object, gen(object)
command. Afterwards, I generated the total number of occurrences with, e.g.,
Code:
egen object9_total = total(object9), by (ID year object9)
command. But what I have now, is that I have 0s in my "object9" variable for those years where the "object9" does not correspond to the same level of my "object" variable. You can see below that I have 0-valued observations for my object9total variable where object is not "glass".


Code:
* Example generated by -dataex-. For more info, type help dataex
clear
input int(ID year) str39 object float object9total
1 2004 "glass" 4
1 2004 "pen"   0
1 2004 "phone" 0
1 2004 "glass" 4
1 2004 "pen"   0
1 2004 "glass" 4
1 2004 "phone" 0
1 2004 "glass" 4
end


So basically, I want to change those 0-valued observations of the newly created variables with the existing values of occurrences of the same object for the given ID in the given year, regardless if object9 corresponds the same level of "object" variable or no.
Below, please see an extraction of my original dataset:

Code:
* Example generated by -dataex-. For more info, type help dataex
clear
input int(ID year) str39 object
 237 2012 "glass"                      
 887 2016 "glass"                      
 430 2013 "glass"                      
 431 2021 "pen"                        
 104 2013 "pen"                        
 198 2021 "glass"                      
 374 2011 "glass"                      
 444 2010 "phone"                      
1073 2016 "glass"                      
 432 2005 "glass"                      
   0 2004 "pen"                        
 555 2017 "glass"                      
1057 2007 "glass"                      
 993 2018 "pen"                        
 815 2011 "pen"                        
 429 2013 "glass"                      
 601 2011 "pen"                        
 701 2000 "glass"                      
  62 2004 "pen"                        
 812 2012 "chair"                      
 273 2016 "charger"                    
 814 2010 "glass"                      
 106 2020 "pen"                        
 264 2012 "knife"                      
 263 2010 "knife"                      
 672 2019 "glass"                      
 902 2011 "glass"                      
 107 2016 "pen"                        
1314 2012 "glass"                      
 434 2010 "pen"                        
 990 2003 "chair"                      
1058 2011 "glass"                      
 406 2004 "phone"                      
 323 2018 "healthcare-related offenses"
 560 2021 "glass"                      
 435 2006 "glass"                      
 436 2009 "pen"                        
  63 2006 "phone"                      
1243 2018 "pen"                        
 908 2019 "phone"                      
 640 2015 "pen"                        
1209 2020 "glass"                      
 108 2019 "glass"                      
 805 2008 "chair"                      
 806 2006 "pen"                        
 109 2008 "pen"                        
 906 2013 "pen"                        
 437 2006 "chair"                      
 673 2008 "phone"                      
 674 2019 "pen"                        
 675 2003 "phone"                      
 698 2010 "glass"                      
 438 2015 "glass"                      
 157 2005 "pen"                        
 395 2013 "glass"                      
1315 2000 "glass"                      
1316 2005 "glass"                      
1080 2011 "phone"                      
1231 2014 "chair"                      
1232 2020 "glass"                      
 676 2017 "glass"                      
 600 2016 "chair"                      
1278 2017 "glass"                      
 951 2002 "glass"                      
 310 2001 "phone"                      
 807 2012 "chair"                      
1320 2005 "glass"                      
 545 2008 "glass"                      
1081 2015 "phone"                      
1065 2006 "glass"                      
 111 2007 "glass"                      
 279 2006 "glass"                      
 371 2007 "phone"                      
 226 2012 "glass"                      
 931 2016 "glass"                      
1321 2002 "table"                      
 773 2000 "glass"                      
 795 2019 "glass"                      
 373 2005 "glass"                      
1323 2018 "table"                      
 554 2018 "glass"                      
 473 2009 "glass"                      
1082 2012 "glass"                      
1311 2021 "phone"                      
 668 2020 "chair"                      
 422 2012 "glass"                      
 611 2014 "table"                      
 989 2018 "charger"                    
1123 2005 "phone"                      
  70 2009 "glass"                      
 583 2012 "glass"                      
 112 2014 "glass"                      
 528 2011 "glass"                      
  90 2015 "table"                      
1322 2002 "glass"                      
1233 2018 "glass"                      
 529 2011 "glass"                      
 537 2000 "glass"                      
 113 2008 "pen"                        
 547 2016 "pen"                        
end

Thank you very much in advance!



Best regards,
Nick