Creating a variable for number of mandates of elected mayors

[Lorenzo De Rosa]

Hi, I have a dataset on elected mayors and I would like to create a variable which states whether the mayor is serving in his/her first, second, or third mandate. To be more precise, I would like to have a variable like the "n_mandate" shown below:

Year id_city id_mayor ElectionYear n_mandate
2008 068001 1 2004 1
2009 068001 1 2009 2
2010 068001 1 2009 2
2011 068001 1 2009 2
2012 068001 1 2009 2
2013 068001 1 2009 2
2014 068001 1 2014 3
2015 068001 1 2014 3
2016 068001 1 2014 3
2017 068001 1 2014 3
2018 068001 1 2014 3
2019 068001 2 2019 1

2010 066001 3 2010 1
2011 066001 3 2010 1
2012 066001 3 2010 1
2013 066001 3 2010 1
2014 066001 3 2010 1
2015 066001 3 2015 2
2016 066001 3 2015 2
2017 066001 3 2015 2
2018 066001 3 2015 2
2019 066001 3 2015 2

2008 066002 4 2004 1
2009 066002 4 2004 1
2010 066002 5 2010 1


Thank you in advance for your help !

[Clyde Schechter]

by id_city id_mayor (year), sort: gen wanted = sum(electionyear!= electionyear[_n-1])

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Lorenzo De Rosa]

Dear Clyde, thank you very much for the code and suggestion. I tried it and it worked perfectly !