Hello. This is my first post and I would really appreciate any help. I have a panel dataset with information on CEO transitions and CEO marital status. What I want to do first is in firms with multiple CEO transitions to create a dummy variable that will indicate only the last transition. The variables I already have are "firm_id", "year", "ceo_id" and "marital_status". Based on this information I want to create the variable "last_ceo_change". After doing this, I want to create two new dummy variables indicating, for the last ceo transition, when we move from a single to a married CEO, as well as when we move from a married to a single CEO (the variables "single_to_married_last_ceo_change" and "married_to_single_last_ceo_change" respectively). Here is how my data looks like:
firm_id year ceo_id marital_status last_ceo_change single_to_married_last_ceo_chang married_to_single_last_ceo_chang
1 2005 456123 1
1 2006 456123 1
1 2007 456123 1
1 2008 389264 0 1 1 0
1 2009 389264 0 1 1 0
1 2010 389264 0 1 1 0
1 2011 389264 0 1 1 0
1 2012 785214 1 1 1 0
1 2013 785214 1 1 1 0
2 2005 654827 1
2 2006 654827 1
2 2007 142536 0
2 2008 142536 0
2 2009 999632 1 1 0 1
2 2010 999632 1 1 0 1
2 2011 885712 0 1 0 1
2 2012 885712 0 1 0 1
2 2013 885712 0 1 0 1
3 2005 986325 0 1 1 0
3 2006 986325 0 1 1 0
3 2007 986325 0 1 1 0
3 2008 986325 0 1 1 0
3 2009 986325 0 1 1 0
3 2010 335964 1 1 1 0
3 2011 335964 1 1 1 0
3 2012 335964 1 1 1 0
3 2013 335964 1 1 1 0
4 2005 235841 0
4 2006 235841 0
4 2007 235841 0
4 2008 689324 0
4 2009 689324 0
4 2010 995423 0 1 1 0
4 2011 995423 0 1 1 0
4 2012 100235 1 1 1 0
4 2013 100235 1 1 1 0

Thank you in advance for your help!