Hi all


My data include

- id and industry are the firm id and the industry of the firm
- first_certified is the first time (year) the company got certification.
- year_certified is the year the company got certification. Firm required to apply and get certification every 2 years. For example, firm id = 4 got certification in 2015 and the firm re-applied and got certification in 2017.

- overall_score is the total score a firm got when certified which is the sum of 6 measures (benefit, community, customer, environment, governance)
- certification_cycle is the order of certification i.e., company id= 4 got certification first time (certification cycle =1) in 2015 and got second certification (certification cycle =2) in 2017
- current status certified/de-certified is at the current time (at the end of 2019)

The database includes more than 2000 companies and 5000 observations.
id industry_id first_certified year_certified overall_score workers benefits community customers environment governance certification_cycle current_status
1 37 2015 2015 80.5 33.7 11 25.4 3.1 9.5 8.8 1 de-certified
2 47 2017 2017 130.4 80.8 29 11.7 8.9 1 certified
3 27 2015 2015 115.3 86.6 23.1 5.6 1 de-certified
4 45 2015 2015 82.2 21.5 47.7 9.1 4 2 certified
4 45 2015 2017 91.9 23.8 10 32.7 14.2 5.6 15.6 1 certified
5 40 2015 2015 111.2 38.1 54.1 4.4 14.5 1 de-certified
6 27 2012 2012 108.6 26.1 9.1 43.6 27.4 11.4 4 certified
6 27 2012 2014 109.8 23.6 7.2 57.3 18.9 9.8 3 certified
6 27 2012 2015 108.9 22.6 10.9 59.1 15.6 11.7 2 certified
6 27 2012 2016 115.6 21.3 8 70.6 11.4 12.3 1 certified
7 27 2013 2013 84.2 22.6 5.5 20.6 30.9 10 3 de-certified
7 27 2013 2015 87.2 39.2 35.4 12.5 2 de-certified
7 27 2013 2017 84.2 15.6 3.2 26.1 25 17.5 1 de-certified
8 61 2019 2019 107.9 24.3 0.5 33.2 38.9 11.3 1 certified
9 45 2017 2017 80.3 30 13 27.7 5.4 7.5 9.7 1 certified
10 34 2014 2014 111 27 3.7 31.5 1.1 42.2 9.3 2 certified
10 34 2014 2016 92.8 23 3.8 25.7 32.9 11.2 1 certified
11 63 2013 2013 92.6 26.9 6 19.1 26.2 7.7 12.4 2 de-certified
11 63 2013 2016 85.4 27.1 8.5 27.3 9 10.1 11.9 1 de-certified
12 5 2013 2013 97.5 26.5 2.3 29 17.5 28.8 13.1 1 de-certified
13 45 2019 2019 82.2 33.8 10.2 24 5.9 7.6 10.7 1 certified
14 45 2016 2016 88.9 48.2 11.7 17.4 11.6 1 de-certified
15 27 2018 2018 104.6 69.5 22.2 12.8 1 certified
16 51 2018 2018 82.1 33.3 9 23.2 11.8 13.7 1 certified
17 22 2018 2018 99.1 20.1 10 25.2 41.7 2.2 9 1 certified
18 36 2014 2014 96.9 22.7 3.7 37.2 29.7 7.1 2 de-certified
18 36 2014 2016 103.6 18.7 3.3 48.2 29.5 7.3 1 de-certified
19 61 2017 2017 104.9 27.9 8.9 23.1 41.3 12.6 1 certified
20 45 2014 2014 99.4 21 6.9 24.7 36.1 9.3 8.3 2 certified
20 45 2014 2016 83.1 25.3 9.5 20.4 21.8 8.2 7.4 1 certified
I would like create summary table to record the (total) number of firms which are first time got certification; firm re-applied and got certification and de-certified. for firm with current status of de-certified we count on the year of the last certified. for example, firm id=7 is count first certified in 2013, re-certified in 2015 and 2017 then de-certified in 2019 (2017+2) which is time the company need to apply if they want to be certified.
year first certified re-certified Discontinue
2007
2008
2009
…
2019
I am new stata. what I can do is to calculate each value in the table and combine them all together. However, it is time consumming and easy to have mistake. Could anyone can give me some direction of how to wite the codes for this

Thank you very much