Help with generating Teacher Turnover rate due to retirement

[Fahad Mirza]

Hello,

Hope you all are doing well.

I have a question regarding a school dataset that has been used to evaluate overall school performance in my country.

Each observation here represents a teacher. Initially, i used rangejoin to calculate how many new teachers have joined and how many old teachers still exist to look at general turnover. Code for it is displayed below:

tempfile copy1
save `"`copy1'"'

rangejoin year -1 -1 using "C:\Users\Fahad Mirza\AppData\Local\Temp\ST_00000001.tmp", by(emiscode cnic)

by emiscode year, sort: egen new_teachers = total(missing(year_U))
by emiscode year: egen old_teachers = count(year_U)

generate turnover_ratio = new_teachers/old_teachers

However, I feel that in this data there is an overall increase in the number of teachers per school and so this is not really showing me exactly what I want.

What I am really looking for is to see how many teachers leave the school (Never come back) due to them reaching their retirement age of 60?

Variable Descriptions:

• 'cnic' is a unique identifier of each person and is the national identity number. This is unique for each teacher
• 'emiscode' is the school code in which a teacher is teaching
• 'age' represents age for each teacher with 60 years being the retirement age
• 'exp_years_sch' represents how many years of teaching experience does a teacher have
• 'exp_years_edu' represents years of experience in an education institute working in administrative posts
• 'year_U' is the variable formed after using rangejoin
• 'new_teachers' tells us difference between year 2012 and 2013 and how many new observations entered in 2013
• 'old_teachers' tells us how many observations remained the same between year 2012 and 2013
• 'turnover_ratio' is simply new_teachers/old_teachers

I will be extremely grateful if the community helps me out with this.

Data for this is stated below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double cnic long emiscode float(year age exp_years_sch exp_years_edu year_U new_teachers old_teachers turnover_ratio)
3110167206901 31110001 2012 22  0  0    . 47  0          .
3110153416505 31110001 2012 45 24 25    . 47  0          .
3110163136687 31110001 2012 21  0  0    . 47  0          .
3110150239283 31110001 2012 45  5 27    . 47  0          .
3110178096533 31110001 2012 28  0  0    . 47  0          .
3110115890277 31110001 2012 48 13 27    . 47  0          .
3110116476493 31110001 2012 43  4 22    . 47  0          .
3110116282891 31110001 2012 37  4 17    . 47  0          .
3110466848689 31110001 2012 51  0 23    . 47  0          .
3110195566095 31110001 2012 48  0 23    . 47  0          .
3110327760389 31110001 2012 35  4  4    . 47  0          .
3110116078381 31110001 2012 46 13 27    . 47  0          .
3110197283181 31110001 2012 39  4 17    . 47  0          .
3110116072647 31110001 2012 53 24 32    . 47  0          .
3110416914419 31110001 2012 52 10 23    . 47  0          .
3110111866121 31110001 2012 32  4  4    . 47  0          .
3110198334703 31110001 2012 43 19 23    . 47  0          .
3110173830503 31110001 2012 52 24 26    . 47  0          .
3110110109477 31110001 2012 50  3 27    . 47  0          .
3110186798927 31110001 2012 41  7 19    . 47  0          .
3110171269761 31110001 2012 48  0 26    . 47  0          .
3110188260735 31110001 2012 43 13 24    . 47  0          .
3110153335415 31110001 2012 41  6 19    . 47  0          .
3110121224181 31110001 2012 32  7 17    . 47  0          .
3110116598281 31110001 2012 46  1 23    . 47  0          .
3110169045709 31110001 2012 42 11 19    . 47  0          .
3110116141093 31110001 2012 39  5 17    . 47  0          .
3110171264405 31110001 2012 39  4 12    . 47  0          .
3110283682189 31110001 2012 46  1 19    . 47  0          .
3110181215211 31110001 2012 35  1 10    . 47  0          .
3110115924171 31110001 2012 49 19 28    . 47  0          .
3110195031739 31110001 2012 45 25 25    . 47  0          .
3110151831419 31110001 2012 58 14 30    . 47  0          .
3110156287185 31110001 2012 44  0 23    . 47  0          .
3110174220469 31110001 2012 44 10 19    . 47  0          .
3110417070793 31110001 2012 57  3 38    . 47  0          .
3110116510889 31110001 2012 51 29 30    . 47  0          .
3110172809805 31110001 2012 32  1 10    . 47  0          .
3110116598635 31110001 2012 56 27 27    . 47  0          .
3110186607123 31110001 2012 50 25 25    . 47  0          .
3110416605521 31110001 2012 53 10 32    . 47  0          .
3110196783399 31110001 2012 25  0  0    . 47  0          .
3110181391401 31110001 2012 45  4 23    . 47  0          .
3110477328073 31110001 2012 47 19 23    . 47  0          .
3110499377877 31110001 2012 48  0 19    . 47  0          .
3110185627593 31110001 2012 51 15 23    . 47  0          .
3110125841229 31110001 2012 51 24 24    . 47  0          .
3110417070793 31110001 2013 58  4 39 2012  3 43 .069767445
3110116598281 31110001 2013 47  2 24 2012  3 43 .069767445
3110110109477 31110001 2013 51  4 28 2012  3 43 .069767445
3110172809805 31110001 2013 33  2 11 2012  3 43 .069767445
3110111866121 31110001 2013 33  5  5 2012  3 43 .069767445
3110195566095 31110001 2013 49  1 24 2012  3 43 .069767445
3110198334703 31110001 2013 44 20 24 2012  3 43 .069767445
3110178096533 31110001 2013 29  1  1 2012  3 43 .069767445
3110185627593 31110001 2013 52 16 24 2012  3 43 .069767445
3110197283181 31110001 2013 40  5 18 2012  3 43 .069767445
3110283682189 31110001 2013 47  2 20 2012  3 43 .069767445
3110156287185 31110001 2013 45  1 24 2012  3 43 .069767445
3110327760389 31110001 2013 36  5  5 2012  3 43 .069767445
3110116282891 31110001 2013 38  5 18 2012  3 43 .069767445
3110153335415 31110001 2013 42  7 20 2012  3 43 .069767445
3110116078381 31110001 2013 47 14 28 2012  3 43 .069767445
3110116476493 31110001 2013 44  5 23 2012  3 43 .069767445
3110499377877 31110001 2013 49  1 20 2012  3 43 .069767445
3110416605521 31110001 2013 54 11 33 2012  3 43 .069767445
3110151831419 31110001 2013 59 15 31 2012  3 43 .069767445
3110167206901 31110001 2013 23  1  1 2012  3 43 .069767445
3110196783399 31110001 2013 26  1  1 2012  3 43 .069767445
3110150239283 31110001 2013 46  6 28 2012  3 43 .069767445
3110174220469 31110001 2013 45 11 20 2012  3 43 .069767445
3110116072647 31110001 2013 54 25 33 2012  3 43 .069767445
3110477328073 31110001 2013 48  4 24 2012  3 43 .069767445
3110171264405 31110001 2013 40  5 13 2012  3 43 .069767445
3110136580845 31110001 2013 48  0 24    .  3 43 .069767445
3110116510889 31110001 2013 52 30 31 2012  3 43 .069767445
3110116141093 31110001 2013 41  6 18 2012  3 43 .069767445
3110163136687 31110001 2013 22  1  1 2012  3 43 .069767445
3110186798927 31110001 2013 42  8 20 2012  3 43 .069767445
3110186607123 31110001 2013 51 26 26 2012  3 43 .069767445
3110115924171 31110001 2013 50 20 29 2012  3 43 .069767445
3110181391401 31110001 2013 46  5 24 2012  3 43 .069767445
3110181215211 31110001 2013 36  2 11 2012  3 43 .069767445
3110125841229 31110001 2013 52 25 25 2012  3 43 .069767445
3110416914419 31110001 2013 53 11 24 2012  3 43 .069767445
3110153416505 31110001 2013 46 25 26 2012  3 43 .069767445
3110188260735 31110001 2013 44 14 25 2012  3 43 .069767445
3110116598635 31110001 2013 57 28 28 2012  3 43 .069767445
3110195031739 31110001 2013 46 26 26 2012  3 43 .069767445
3110417103359 31110001 2013 54  0 27    .  3 43 .069767445
3110171269761 31110001 2013 49  1 27 2012  3 43 .069767445
3110121224181 31110001 2013 33  8 18 2012  3 43 .069767445
3110503014237 31110001 2013 43  1 16    .  3 43 .069767445
3110112817327 31110002 2012 48 19 19    . 40  0          .
3110170215205 31110002 2012 46  3 23    . 40  0          .
3110165771043 31110002 2012 43  5 19    . 40  0          .
3110196417395 31110002 2012 44 13 25    . 40  0          .
3110254879170 31110002 2012 49  3 17    . 40  0          .
3110158027017 31110002 2012 39  3 10    . 40  0          .
3110116470733 31110002 2012 50 15 28    . 40  0          .
3110108906733 31110002 2012 42  3 19    . 40  0          .
3110171945591 31110002 2012 24  0  0    . 40  0          .
3110115960921 31110002 2012 40 12 17    . 40  0          .
3110194854512 31110002 2012 44  1 19    . 40  0          .
3110175755695 31110002 2012 47  5 17    . 40  0          .
3110141585131 31110002 2012 43 13 19    . 40  0          .
3110116533155 31110002 2012 36  7  7    . 40  0          .
3110247915523 31110002 2012 47  9 23    . 40  0          .
3110116028151 31110002 2012 42  1 10    . 40  0          .
3110135121161 31110002 2012 57 19 26    . 40  0          .
3120267984009 31110002 2012 45  2 22    . 40  0          .
3110155814125 31110002 2012 51 27 27    . 40  0          .
3110124158951 31110002 2012 40 15 15    . 40  0          .
3110206097015 31110002 2012 45  4 25    . 40  0          .
3110172564953 31110002 2012 38  7  8    . 40  0          .
3110116146559 31110002 2012 49  3 17    . 40  0          .
3110180564923 31110002 2012 47  5 27    . 40  0          .
3110139696107 31110002 2012 55 15 36    . 40  0          .
3110115884425 31110002 2012 56  2 25    . 40  0          .
3110184964285 31110002 2012 27  0  0    . 40  0          .
3110116571755 31110002 2012 49  0 27    . 40  0          .
3110118988476 31110002 2012 50 13 25    . 40  0          .
3110486269046 31110002 2012 33  4  9    . 40  0          .
3110108362261 31110002 2012 40  5 17    . 40  0          .
3110184891271 31110002 2012 37  5 15    . 40  0          .
3110172640601 31110002 2012 48  1 25    . 40  0          .
3110116380565 31110002 2012 35  0  8    . 40  0          .
3110371325365 31110002 2012 34  1  1    . 40  0          .
3110235493971 31110002 2012 39  2 28    . 40  0          .
3110266097075 31110002 2012 34  7  7    . 40  0          .
3110116380559 31110002 2012 41 13 19    . 40  0          .
3110116540873 31110002 2012 35  0  3    . 40  0          .
3110340455053 31110002 2012 55  9 28    . 40  0          .
3110235493371 31110002 2013 40  3 18    . 20 22   .9090909
3110197422937 31110002 2013 49 23 26    . 20 22   .9090909
3110101692723 31110002 2013 49  0 26    . 20 22   .9090909
3110155814125 31110002 2013 52 28 28 2012 20 22   .9090909
3110184964285 31110002 2013 28  1  1 2012 20 22   .9090909
3110206097015 31110002 2013 46  5 26 2012 20 22   .9090909
3110116533155 31110002 2013 37  8  8 2012 20 22   .9090909
3110172564953 31110002 2013 39  8  9 2012 20 22   .9090909
3110115960421 31110002 2013 41 13 18    . 20 22   .9090909
3110124891273 31110002 2013 38  6 16    . 20 22   .9090909
3110108362261 31110002 2013 41  6 18 2012 20 22   .9090909
3110196417395 31110002 2013 45 14 26 2012 20 22   .9090909
3120267984009 31110002 2013 46  3 23 2012 20 22   .9090909
3110135211161 31110002 2013 58 20 27    . 20 22   .9090909
3110124158951 31110002 2013 41  5 16 2012 20 22   .9090909
3110116470733 31110002 2013 51 14 29 2012 20 22   .9090909
3110247915523 31110002 2013 48 10 24 2012 20 22   .9090909
3110116571755 31110002 2013 50  1 28 2012 20 22   .9090909
3110158027017 31110002 2013 40  4 11 2012 20 22   .9090909
3110165771043 31110002 2013 44  6 20 2012 20 22   .9090909
3110132859021 31110002 2013 51  0 26    . 20 22   .9090909
3110180564923 31110002 2013 48  6 28 2012 20 22   .9090909
3110115887425 31110002 2013 57  3 33    . 20 22   .9090909
3110116380559 31110002 2013 42  7 20 2012 20 22   .9090909
3110286269076 31110002 2013 50  2 26    . 20 22   .9090909
3110271945595 31110002 2013 25  1  1    . 20 22   .9090909
3110115851361 31110002 2013 44 14 20    . 20 22   .9090909
3110116028751 31110002 2013 43  3 11    . 20 22   .9090909
3110340395053 31110002 2013 56 10 29    . 20 22   .9090909
3110116380565 31110002 2013 36  1  9 2012 20 22   .9090909
3110189884776 31110002 2013 51 14 26    . 20 22   .9090909
3110116471847 31110002 2013 45  2 20    . 20 22   .9090909
3110116540813 31110002 2013 36  1  4    . 20 22   .9090909
3110170215205 31110002 2013 47  4 24 2012 20 22   .9090909
3110205787673 31110002 2013 35 10 10    . 20 22   .9090909
3110108916733 31110002 2013 43  4 20    . 20 22   .9090909
3110175755695 31110002 2013 48  6 18 2012 20 22   .9090909
3110186542235 31110002 2013 34  5 10    . 20 22   .9090909
3110111325365 31110002 2013 35  2 11    . 20 22   .9090909
3110116146559 31110002 2013 50  4 18 2012 20 22   .9090909
3110139696107 31110002 2013 56 16 37 2012 20 22   .9090909
3110112817327 31110002 2013 49 20 20 2012 20 22   .9090909
3110103474737 31110003 2012 43  9 15    . 19  0          .
3110177262691 31110003 2012 24  0  0    . 19  0          .
3110420712953 31110003 2012 41  5 17    . 19  0          .
3110118856521 31110003 2012 43 18 19    . 19  0          .
3110106815959 31110003 2012 43 20 25    . 19  0          .
3110116417469 31110003 2012 40 15 15    . 19  0          .
3110116070061 31110003 2012 31  1  1    . 19  0          .
3110183014563 31110003 2012 47 11 27    . 19  0          .
3110116027191 31110003 2012 44  0 17    . 19  0          .
3110115062231 31110003 2012 39  1  6    . 19  0          .
3110192080483 31110003 2012 34  0  3    . 19  0          .
3110421319009 31110003 2012 47  0 19    . 19  0          .
3110198843529 31110003 2012 32  2  2    . 19  0          .
3110152663723 31110003 2012 28  0  0    . 19  0          .
3110192145649 31110003 2012 31  0  0    . 19  0          .
3110318252843 31110003 2012 55  7 32    . 19  0          .
3110181414993 31110003 2012 36  9 12    . 19  0          .
3110160151581 31110003 2012 48 14 27    . 19  0          .
3110132683767 31110003 2012 41  0 13    . 19  0          .
3110115062231 31110003 2013 40  2  7 2012  4 15  .26666668
3110421319009 31110003 2013 48  1 20 2012  4 15  .26666668
3110183014563 31110003 2013 48 12 28 2012  4 15  .26666668
3110420712953 31110003 2013 42  6 18 2012  4 15  .26666668
3110116070061 31110003 2013 32  2  2 2012  4 15  .26666668
3110116027191 31110003 2013 45  2 18 2012  4 15  .26666668
3110177262691 31110003 2013 25  1  1 2012  4 15  .26666668
3110116417469 31110003 2013 41 16 16 2012  4 15  .26666668
3110160151581 31110003 2013 49 15 28 2012  4 15  .26666668
3110192145649 31110003 2013 32  1  1 2012  4 15  .26666668
3110152663723 31110003 2013 29  1  1 2012  4 15  .26666668
3110168843529 31110003 2013 33  3  3    .  4 15  .26666668
3110103474733 31110003 2013 44 10 16    .  4 15  .26666668
3110106815959 31110003 2013 44 21 26 2012  4 15  .26666668
3110118856521 31110003 2013 44 19 20 2012  4 15  .26666668
3110132983767 31110003 2013 42  1 14    .  4 15  .26666668
3110192080481 31110003 2013 35  1  1    .  4 15  .26666668
3110318252843 31110003 2013 56  . 33 2012  4 15  .26666668
3110181414993 31110003 2013 37 10 13 2012  4 15  .26666668
3110116565971 31110004 2012 34  3  3    . 15  0          .
3110127375491 31110004 2012 54 16 27    . 15  0          .
3110116566119 31110004 2012 55  2 19    . 15  0          .
3110108166471 31110004 2012 22  0  0    . 15  0          .
3110116009875 31110004 2012 34 24 24    . 15  0          .
3110116658729 31110004 2012 31  6  6    . 15  0          .
3110182386669 31110004 2012 48 23 23    . 15  0          .
3110115881143 31110004 2012 29  3  3    . 15  0          .
3110134476197 31110004 2012 27  0  0    . 15  0          .
3110116624339 31110004 2012 48  5 24    . 15  0          .
3110116658227 31110004 2012 38  1  3    . 15  0          .
3660118466323 31110004 2012 21  0  0    . 15  0          .
3110105637517 31110004 2012 61  3 33    . 15  0          .
3110116596703 31110004 2012 54  3 34    . 15  0          .
3110116374817 31110004 2012 54 11 30    . 15  0          .
3110116596703 31110004 2013 55  4 35 2012  3 12        .25
3110116658729 31110004 2013 32  7  7 2012  3 12        .25
3110182386669 31110004 2013 49 24 24 2012  3 12        .25
3110127375491 31110004 2013 55 17 28 2012  3 12        .25
3110145442423 31110004 2013 29  0  0    .  3 12        .25
3110115881143 31110004 2013 30  4  4 2012  3 12        .25
3110116624339 31110004 2013 49  6 25 2012  3 12        .25
3110116009875 31110004 2013 35 25 25 2012  3 12        .25
3110116374817 31110004 2013 55 12 31 2012  3 12        .25
3110108166471 31110004 2013 23  1  1 2012  3 12        .25
3110116566113 31110004 2013 56 13 20    .  3 12        .25
3110118466323 31110004 2013 22  1  1    .  3 12        .25
3110116565971 31110004 2013 35  4  4 2012  3 12        .25
3110116658227 31110004 2013 39  2  4 2012  3 12        .25
3110134476197 31110004 2013 28  1  1 2012  3 12        .25
end

[Clyde Schechter]

Well it is not clear what is going on. Evidently retirement is not mandatory at age 60, or else your data have errors as there is at least one observation with a person working at age 61. So I would imagine that what you mean by a retirement turnover is that in the last year that a person works, he or she is age 60. (Or would that be 59?) Anyway, based on that, the code would be:

by cnic (year), sort: gen byte retired = _n == _N & age == 60
rangestat (sum) school_retired_teachers = retired, by(emiscode) interval(year -1 -1)

But, in your example data, there are no observations that fit my definition of a retirement turnover. So I'm not sure if this is really what you need or not.

[Fahad Mirza]

Hello Clyde,

Thank you for the code however it seems to not be doing what i require as my question was not phrased correctly.

My question is that for example a teacher is of the age 59 in year 2012. The law states that as soon as the person touches the age of 60, he/she has to retire. Which means anyone in 2012 at the age of 59 will be 60 in year 2013 and so will have to be replaced by someone younger.

My question now is that when this teacher retires, who is he/she replaced by? That should just be a binary indication of 0 or 1 which I have been trying to figure out for a while now but I just cant get my mind to solve this problem. This is how I would like to look at retirement turnover.

That way i can see how many new people entered 2013 because someone retired.

I hope my question is making sense now. Please do let me know if you require further clarification.

Also, once again thank you for helping out!

[Fahad Mirza]

Also, regarding the observation having value of 61 for age. That is an entry error and you may ignore that as the data has not been cleaned for the age variable mainly because the surveyor entered incorrect year of birth for many people and so causing their age to reach even 2000 years

[Fahad Mirza]

apologies for posting again as i am really trying to understand how to go about solving this problem

[Clyde Schechter]

Sorry, I didn't see your earlier replies. But, in any case, I don't understand what is needed. There is nothing in the data (or, if there is, it isn't in your example) that would tell you whether a new teacher is there because somebody else retired. If you want to assume that if X people retire and Y people are hired, then, if X <= Y, X of the Y new hires are replacements for the retirees, then that can be calculated. (If X > Y, then all Y could be considered replacements).

The following code will (almost) give you a data set with one observation per school per year showing the number of new teachers (those not employed at the school in the preceding year), and the number of retirees from the previous year. I say "almost" because for the first year in the data set, there is no way to determine who is new and who is held over from an earlier year that has no data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double cnic long emiscode float(year age)
3110167206901 31110001 2012 22
3110153416505 31110001 2012 45
3110163136687 31110001 2012 21
3110150239283 31110001 2012 45
3110178096533 31110001 2012 28
3110115890277 31110001 2012 48
3110116476493 31110001 2012 43
3110116282891 31110001 2012 37
3110466848689 31110001 2012 51
3110195566095 31110001 2012 48
3110327760389 31110001 2012 35
3110116078381 31110001 2012 46
3110197283181 31110001 2012 39
3110116072647 31110001 2012 53
3110416914419 31110001 2012 52
3110111866121 31110001 2012 32
3110198334703 31110001 2012 43
3110173830503 31110001 2012 52
3110110109477 31110001 2012 50
3110186798927 31110001 2012 41
3110171269761 31110001 2012 48
3110188260735 31110001 2012 43
3110153335415 31110001 2012 41
3110121224181 31110001 2012 32
3110116598281 31110001 2012 46
3110169045709 31110001 2012 42
3110116141093 31110001 2012 39
3110171264405 31110001 2012 39
3110283682189 31110001 2012 46
3110181215211 31110001 2012 35
3110115924171 31110001 2012 49
3110195031739 31110001 2012 45
3110151831419 31110001 2012 58
3110156287185 31110001 2012 44
3110174220469 31110001 2012 44
3110417070793 31110001 2012 57
3110116510889 31110001 2012 51
3110172809805 31110001 2012 32
3110116598635 31110001 2012 56
3110186607123 31110001 2012 50
3110416605521 31110001 2012 53
3110196783399 31110001 2012 25
3110181391401 31110001 2012 45
3110477328073 31110001 2012 47
3110499377877 31110001 2012 48
3110185627593 31110001 2012 51
3110125841229 31110001 2012 51
3110417070793 31110001 2013 58
3110116598281 31110001 2013 47
3110110109477 31110001 2013 51
3110172809805 31110001 2013 33
3110111866121 31110001 2013 33
3110195566095 31110001 2013 49
3110198334703 31110001 2013 44
3110178096533 31110001 2013 29
3110185627593 31110001 2013 52
3110197283181 31110001 2013 40
3110283682189 31110001 2013 47
3110156287185 31110001 2013 45
3110327760389 31110001 2013 36
3110116282891 31110001 2013 38
3110153335415 31110001 2013 42
3110116078381 31110001 2013 47
3110116476493 31110001 2013 44
3110499377877 31110001 2013 49
3110416605521 31110001 2013 54
3110151831419 31110001 2013 59
3110167206901 31110001 2013 23
3110196783399 31110001 2013 26
3110150239283 31110001 2013 46
3110174220469 31110001 2013 45
3110116072647 31110001 2013 54
3110477328073 31110001 2013 48
3110171264405 31110001 2013 40
3110136580845 31110001 2013 48
3110116510889 31110001 2013 52
3110116141093 31110001 2013 41
3110163136687 31110001 2013 22
3110186798927 31110001 2013 42
3110186607123 31110001 2013 51
3110115924171 31110001 2013 50
3110181391401 31110001 2013 46
3110181215211 31110001 2013 36
3110125841229 31110001 2013 52
3110416914419 31110001 2013 53
3110153416505 31110001 2013 46
3110188260735 31110001 2013 44
3110116598635 31110001 2013 57
3110195031739 31110001 2013 46
3110417103359 31110001 2013 54
3110171269761 31110001 2013 49
3110121224181 31110001 2013 33
3110503014237 31110001 2013 43
3110112817327 31110002 2012 48
3110170215205 31110002 2012 46
3110165771043 31110002 2012 43
3110196417395 31110002 2012 44
3110254879170 31110002 2012 49
3110158027017 31110002 2012 39
3110116470733 31110002 2012 50
3110108906733 31110002 2012 42
3110171945591 31110002 2012 24
3110115960921 31110002 2012 40
3110194854512 31110002 2012 44
3110175755695 31110002 2012 47
3110141585131 31110002 2012 43
3110116533155 31110002 2012 36
3110247915523 31110002 2012 47
3110116028151 31110002 2012 42
3110135121161 31110002 2012 57
3120267984009 31110002 2012 45
3110155814125 31110002 2012 51
3110124158951 31110002 2012 40
3110206097015 31110002 2012 45
3110172564953 31110002 2012 38
3110116146559 31110002 2012 49
3110180564923 31110002 2012 47
3110139696107 31110002 2012 55
3110115884425 31110002 2012 56
3110184964285 31110002 2012 27
3110116571755 31110002 2012 49
3110118988476 31110002 2012 50
3110486269046 31110002 2012 33
3110108362261 31110002 2012 40
3110184891271 31110002 2012 37
3110172640601 31110002 2012 48
3110116380565 31110002 2012 35
3110371325365 31110002 2012 34
3110235493971 31110002 2012 39
3110266097075 31110002 2012 34
3110116380559 31110002 2012 41
3110116540873 31110002 2012 35
3110340455053 31110002 2012 55
3110235493371 31110002 2013 40
3110197422937 31110002 2013 49
3110101692723 31110002 2013 49
3110155814125 31110002 2013 52
3110184964285 31110002 2013 28
3110206097015 31110002 2013 46
3110116533155 31110002 2013 37
3110172564953 31110002 2013 39
3110115960421 31110002 2013 41
3110124891273 31110002 2013 38
3110108362261 31110002 2013 41
3110196417395 31110002 2013 45
3120267984009 31110002 2013 46
3110135211161 31110002 2013 58
3110124158951 31110002 2013 41
3110116470733 31110002 2013 51
3110247915523 31110002 2013 48
3110116571755 31110002 2013 50
3110158027017 31110002 2013 40
3110165771043 31110002 2013 44
3110132859021 31110002 2013 51
3110180564923 31110002 2013 48
3110115887425 31110002 2013 57
3110116380559 31110002 2013 42
3110286269076 31110002 2013 50
3110271945595 31110002 2013 25
3110115851361 31110002 2013 44
3110116028751 31110002 2013 43
3110340395053 31110002 2013 56
3110116380565 31110002 2013 36
3110189884776 31110002 2013 51
3110116471847 31110002 2013 45
3110116540813 31110002 2013 36
3110170215205 31110002 2013 47
3110205787673 31110002 2013 35
3110108916733 31110002 2013 43
3110175755695 31110002 2013 48
3110186542235 31110002 2013 34
3110111325365 31110002 2013 35
3110116146559 31110002 2013 50
3110139696107 31110002 2013 56
3110112817327 31110002 2013 49
3110103474737 31110003 2012 43
3110177262691 31110003 2012 24
3110420712953 31110003 2012 41
3110118856521 31110003 2012 43
3110106815959 31110003 2012 43
3110116417469 31110003 2012 40
3110116070061 31110003 2012 31
3110183014563 31110003 2012 47
3110116027191 31110003 2012 44
3110115062231 31110003 2012 39
3110192080483 31110003 2012 34
3110421319009 31110003 2012 47
3110198843529 31110003 2012 32
3110152663723 31110003 2012 28
3110192145649 31110003 2012 31
3110318252843 31110003 2012 55
3110181414993 31110003 2012 36
3110160151581 31110003 2012 48
3110132683767 31110003 2012 41
3110115062231 31110003 2013 40
3110421319009 31110003 2013 48
3110183014563 31110003 2013 48
3110420712953 31110003 2013 42
3110116070061 31110003 2013 32
3110116027191 31110003 2013 45
3110177262691 31110003 2013 25
3110116417469 31110003 2013 41
3110160151581 31110003 2013 49
3110192145649 31110003 2013 32
3110152663723 31110003 2013 29
3110168843529 31110003 2013 33
3110103474733 31110003 2013 44
3110106815959 31110003 2013 44
3110118856521 31110003 2013 44
3110132983767 31110003 2013 42
3110192080481 31110003 2013 35
3110318252843 31110003 2013 56
3110181414993 31110003 2013 37
3110116565971 31110004 2012 34
3110127375491 31110004 2012 54
3110116566119 31110004 2012 55
3110108166471 31110004 2012 22
3110116009875 31110004 2012 34
3110116658729 31110004 2012 31
3110182386669 31110004 2012 48
3110115881143 31110004 2012 29
3110134476197 31110004 2012 27
3110116624339 31110004 2012 48
3110116658227 31110004 2012 38
3660118466323 31110004 2012 21
3110105637517 31110004 2012 61
3110116596703 31110004 2012 54
3110116374817 31110004 2012 54
3110116596703 31110004 2013 55
3110116658729 31110004 2013 32
3110182386669 31110004 2013 49
3110127375491 31110004 2013 55
3110145442423 31110004 2013 29
3110115881143 31110004 2013 30
3110116624339 31110004 2013 49
3110116009875 31110004 2013 35
3110116374817 31110004 2013 55
3110108166471 31110004 2013 23
3110116566113 31110004 2013 56
3110118466323 31110004 2013 22
3110116565971 31110004 2013 35
3110116658227 31110004 2013 39
3110134476197 31110004 2013 28
end

isid cnic year
tempfile original
save `original'

//  SHOW NUMBER OF TEACHERS AT EACH SCHOOL EACH YEAR
collapse (count) n_teachers = cnic, by(emiscode year)
tempfile schools
save `schools'

//  FIND NUMBER OF RETIREES AT EACH SCHOOL EACH YEAR
rangejoin year -1 -1 using `original', by(emiscode)
drop year_U
merge m:1 emiscode year using `schools', assert(match using) nogenerate
by emiscode year, sort: egen retirees = total(age == 60)
by emiscode year: keep if _n == 1
tempfile holding
save `holding', replace

//  FIND NUMBER OF NEW TEACHERS AT EACH SCHOOL EACH YEAR
use `original', clear
by cnic (year), sort: gen byte is_new = emiscode != emiscode[_n-1]
collapse (sum) new_teachers = is_new, by(emiscode year)
by emiscode (year), sort: replace new_teachers = . if _n == 1

//  PUT IT ALL TOGETHER
merge 1:1 emiscode year using `holding', assert(match) nogenerate

Note: In your example data, there are no 60 year old teachers, so the results show no retirements.

I hope this helps.

[Fahad Mirza]

Hello,

Thank you so much for the detailed response, will go through this and let you know how it goes but thanks once again. I really appreciate it!

Also, apologies for being pushy on this, wasnt really thinking it through properly