Calculating movement of students across grades

[Chirag Yadav]

I have a student-level dataset that shows their school and grade for each year. I want to understand the movements of students across schools.

Here is an example dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(studentid schoolid grade) int year float h_grade
1 10  1 2000  5
1 10  2 2001  5
1 11  3 2002  8
1 10  4 2003  5
2 10  3 2000  5
2 10  4 2001  5
2 10  5 2002  5
2 11  6 2003  8
2 11  7 2004  8
2 11  8 2005  8
2 21  9 2006 12
3 11  1 2002  8
3 11  2 2003  8
3 11  3 2004  8
3 11  4 2005  8
3 11  5 2006  8
3 11  6 2007  8
3 21  7 2008 12
3 21  8 2009 12
3 15  9 2010 12
3 15 10 2011 12
3 15 11 2012 12
3 15 12 2013 12
4 11  5 2006  8
4 11  6 2007  8
4 11  6 2008  8
4 21  7 2009 12
4 21  8 2010 12
4 21  9 2011 12
5 21  4 2001 12
5 21  5 2002 12
5 21  6 2003 12
5 21  6 2004 12
5 15  7 2005 12
5 15  8 2006 12
5 15  9 2007 12
5 21 10 2008 12
5 21 11 2009 12
5 21 12 2010 12
end

Using this I have created a shift variable for structural shifts (school changes due to school not offering the grade or closing down) and nonstructural shifts (school changes due to any other reason) and a variable for percentage of nonstructural shifts into each school by grade and year:

**Creating shift variable**
bys schoolid: egen sid_last_yr = max(year)
sort studentid year
by studentid: gen lag_sid = schoolid[_n-1]
by studentid: gen lag_hgrade = h_grade[_n-1]
by studentid: gen lag_sid_last_yr = sid_last_yr[_n-1]

by studentid: gen shift = (schoolid != lag_sid) if lag_sid!=.
replace shift = 2 if shift==1&(grade>lag_hgrade) // Move due to grade not being offereda in current school
replace shift = 2 if shift==1&(year>lag_sid_last_yr) // Move due to grade not being offered in current school

label define shift 0 "Stay" 1 "Nonstructural shift" 2 "Structural shift"
label values shift shift

**Total students in school in each year and grade**
sort studentid grade schoolid year
egen tag = tag(studentid grade schoolid year)
egen tot_stu = total(tag), by(grade schoolid year)
drop tag

**Total nonstructural in school in each year and grade**
bys grade schoolid year: egen tot_ns_move_temp = total(shift) if shift==1
bys grade schoolid year: egen tot_ns_move = max(tot_ns_move_temp) if shift!=.
drop tot_ns_move_temp

gen per_ns_move = tot_ns_move/tot_stu

However, as a next step I need a variable that shows nonstructural shifts out of schools in each grade in each year. For ex, NS shifts out of grade 3 this year = (Students not in this school in grade 3 this year who were in grade 2 last year / Total students last year in grade 2).

First, to get the denominator, how can I create a variable that counts students in previous grade in previous year? I tried this but it gives me incorrect output:

sort studentid year
by studentid: gen lag_tot_stu = tot_stu[_n-1]

For the numerator, I tried doing what I did to create tot_ns_move but this time sorting by lag_campus. That gives incorrect output as well.

[Daniel Schaefer]

So basically you want the number of nonstructural shifts this year over the number of students last year in each grade and school, right?

The shift variable indicates whether a student shifted schools this year. Let's just move the values for that variable back a year, or equivalently, create a variable that indicates a shift next year. Then we can count up the number of shifts next year and divide by the total number of students in the relevant year.

* move shift back one year
bys studentid (year): gen shift_next_year = shift[_n+1]
* count number of shifts in a given school, year, and grade
bys schoolid year grade: gen count_ns_shifts_next_year = total(shift_next_year == 1)
* divide by the total number of students in the school, year, and grade
gen wanted = count_ns_shifts_next_year / tot_stu

If necessary, you can then shift these totals forward a year in the usual way.

bys studentid (year): replace wanted = wanted[_n-1]

Of course, this assumes that studentid, year pairs uniquely identify the data.

Edit: Looks like nonstructural shifts are coded as 1, not 2. I've edited the code above to reflect that.

[Daniel Schaefer]

Sorry, I should have tested carefully before posting. The -replace- command does not behave as I expect in the last line above. Seems like the best way is to just create a new variable rather than trying to write over the old one:

bys studentid (year): generate really_wanted = wanted[_n-1]

[Chirag Yadav]

That worked! Thank you!