Create Multiple Binary Variables to Indicate When Students were Retained in Grades in Stata?

[smith Jason]

Hi, Folks,
I have a dataset like this,
clear
input str10 id byte state byte year byte gr
001 0 1 0
001 0 2 1
001 0 3 3
001 1 4 2
002 0 1 0
002 0 2 1
002 0 3 2
002 0 4 4
002 1 5 3
002 0 6 6
002 1 7 5
002 1 8 6
003 0 1 0
003 0 2 1
003 0 3 2
003 0 4 3
003 0 5 4
003 . 6 .
003 0 7 7
003 1 8 6
003 0 9 8
003 . 10 .
003 0 11 10
003 0 12 11
004 0 1 0
004 . 2 .
004 0 3 2
end

As the title of the thread suggested, I want to create 3 binary variables to indicate at which stage where the students were retained in grades in K-12 system.
Here gr==grade_year, state==whether a student was retained in grades.
For the student with ID==1, he was demoted and still regarded as a student retained in grades.

The rule is listed below for reference,
1) Within each id, if state==1 and year<=5, then all variables of primaryschool==1, otherwise primary==0
2) Within each id, if state==1 and year ranged from 6 to 8, then all variables of middleschool==1, otherwise middle==0
3) Within each id, if state==1 and year ranged from 9 to 12, then all variables of highschool==1, otherwise high==0
4) Within each id, if state consists of missing values and zero, then the corresponding stage should be "missing value".

Thank you for your kindly code!

[Carlo Lazzaro]

Jason:
do you mean something along the following lines (9999=missing)?

. bysort id: gen primaryschool=1 if year<=5 & !missing(id, state, year)

. bysort id: replace primaryschool=9999 if year<=5 & missing(id, state, year)

. replace primaryschool=0 if primaryschool==.

. bysort id: gen middleschool=1 if year>=6 & year<=8 & !missing(id, state, year)

. bysort id: replace middleschool=9999 if year>=6 & year<=8 & missing(id, state, year)

. replace middleschool=0 if middleschool==.

. bysort id: gen highschool=1 if year>=9 & year<=12 & !missing(id, state, year)

. bysort id: replace highschool=9999 if year>=9 & year<=12 & missing(id, state, year)

. replace highschool=0 if highschool==.

. bysort id: replace primaryschool=0 if state==0

. bysort id: replace middleschool=0 if state==0

. bysort id: replace highschool=0 if state==0

. list

     +----------------------------------------------------------+
     |  id   state   year   gr   primar~l   middle~l   highsc~l |
     |----------------------------------------------------------|
  1. | 001       0      1    0          0          0          0 |
  2. | 001       0      2    1          0          0          0 |
  3. | 001       0      3    3          0          0          0 |
  4. | 001       1      4    2          1          0          0 |
  5. | 002       0      1    0          0          0          0 |
     |----------------------------------------------------------|
  6. | 002       0      2    1          0          0          0 |
  7. | 002       0      3    2          0          0          0 |
  8. | 002       0      4    4          0          0          0 |
  9. | 002       1      5    3          1          0          0 |
 10. | 002       0      6    6          0          0          0 |
     |----------------------------------------------------------|
 11. | 002       1      7    5          0          1          0 |
 12. | 002       1      8    6          0          1          0 |
 13. | 003       0      1    0          0          0          0 |
 14. | 003       0      2    1          0          0          0 |
 15. | 003       0      3    2          0          0          0 |
     |----------------------------------------------------------|
 16. | 003       0      4    3          0          0          0 |
 17. | 003       0      5    4          0          0          0 |
 18. | 003       .      6    .          0       9999          0 |
 19. | 003       0      7    7          0          0          0 |
 20. | 003       1      8    6          0          1          0 |
     |----------------------------------------------------------|
 21. | 003       0      9    8          0          0          0 |
 22. | 003       .     10    .          0          0       9999 |
 23. | 003       0     11   10          0          0          0 |
 24. | 003       0     12   11          0          0          0 |
 25. | 004       0      1    0          0          0          0 |
     |----------------------------------------------------------|
 26. | 004       .      2    .       9999          0          0 |
 27. | 004       0      3    2          0          0          0 |
     +----------------------------------------------------------+

.

[smith Jason]

Thank you very much!

[smith Jason]

Jason:
do you mean something along the following lines (9999=missing)?

. bysort id: gen primaryschool=1 if year<=5 & !missing(id, state, year)

. bysort id: replace primaryschool=9999 if year<=5 & missing(id, state, year)

. replace primaryschool=0 if primaryschool==.

. bysort id: gen middleschool=1 if year>=6 & year<=8 & !missing(id, state, year)

. bysort id: replace middleschool=9999 if year>=6 & year<=8 & missing(id, state, year)

. replace middleschool=0 if middleschool==.

. bysort id: gen highschool=1 if year>=9 & year<=12 & !missing(id, state, year)

. bysort id: replace highschool=9999 if year>=9 & year<=12 & missing(id, state, year)

. replace highschool=0 if highschool==.

. bysort id: replace primaryschool=0 if state==0

. bysort id: replace middleschool=0 if state==0

. bysort id: replace highschool=0 if state==0

. list

+----------------------------------------------------------+
| id state year gr primar~l middle~l highsc~l |
|----------------------------------------------------------|
1. | 001 0 1 0 0 0 0 |
2. | 001 0 2 1 0 0 0 |
3. | 001 0 3 3 0 0 0 |
4. | 001 1 4 2 1 0 0 |
5. | 002 0 1 0 0 0 0 |
|----------------------------------------------------------|
6. | 002 0 2 1 0 0 0 |
7. | 002 0 3 2 0 0 0 |
8. | 002 0 4 4 0 0 0 |
9. | 002 1 5 3 1 0 0 |
10. | 002 0 6 6 0 0 0 |
|----------------------------------------------------------|
11. | 002 1 7 5 0 1 0 |
12. | 002 1 8 6 0 1 0 |
13. | 003 0 1 0 0 0 0 |
14. | 003 0 2 1 0 0 0 |
15. | 003 0 3 2 0 0 0 |
|----------------------------------------------------------|
16. | 003 0 4 3 0 0 0 |
17. | 003 0 5 4 0 0 0 |
18. | 003 . 6 . 0 9999 0 |
19. | 003 0 7 7 0 0 0 |
20. | 003 1 8 6 0 1 0 |
|----------------------------------------------------------|
21. | 003 0 9 8 0 0 0 |
22. | 003 . 10 . 0 0 9999 |
23. | 003 0 11 10 0 0 0 |
24. | 003 0 12 11 0 0 0 |
25. | 004 0 1 0 0 0 0 |
|----------------------------------------------------------|
26. | 004 . 2 . 9999 0 0 |
27. | 004 0 3 2 0 0 0 |
+----------------------------------------------------------+

.

I think that I didn't state clearly about the missing value.
For example, for the student with ID==4, all the corresponding value of "primaryschool" variable should be "missing values".

1) Within each id, if state==1 and year<=5, then all variables of primaryschool==1, otherwise primary==0 (not just one value of primaryschool==1)
2) Within each id, if state==1 and year ranged from 6 to 8, then all variables of middleschool==1, otherwise middle==0 ((not just one value of middleschool==1)
3) Within each id, if state==1 and year ranged from 9 to 12, then all variables of highschool==1, otherwise high==0 ((not just one value of highschool==1)


Thank you for your kindly help with the code!

[Carlo Lazzaro]

Jason:
hence the first three observations for id=1 should be missinng as well (state=0)?

[smith Jason]

No. Because student 0001 is a demoted student, he/she demoted from grade 3 to grade 2, (0–kindergarten). Therefore, when state=0, first three observations of primarschool for id=1 should be 1. Demoted student is regarded as a student who was retained in grades.

[Carlo Lazzaro]

Jason:
sorry, but being totally unfamliar with the education system you're referring to, I do not have better suggestions.

[smith Jason]

Thank you!
it is just a data error. Please ignore the education background.

[smith Jason]

Jason:
sorry, but being totally unfamliar with the education system you're referring to, I do not have better suggestions.

Your code almost get what I want. However, I really struck there and don't know how to solve this data error.

[Carlo Lazzaro]

Jason:
hopefully what follows is what you're after:

. bysort id: gen primaryschool=1 if year<=5 & !missing(id, state, year)

. bysort id: replace primaryschool=9999 if year<=5 & missing(id, state, year)

. bysort id (primaryschool): replace primaryschool=9999 if primaryschool[_N]==9999

. bysort id: replace primaryschool=0 if primaryschool==.

. bysort id: gen middleschool=1 if year>=6 & year<=8 & !missing(id, state, year)

. bysort id: replace middleschool=1 if year>=6 & year<=8 & missing(id, state, year)

. bysort id: replace middleschool=0 if middleschool==.

. bysort id: gen highschool=1 if year>=9 & year<=12 & !missing(id, state, year)

.  bysort id: replace highschool=9999 if year>=9 & year<=12 & missing(id, state, year)

. replace highschool=0 if highschool==.

. bysort id (highschool): replace highschool=9999 if year>=9 & year<=12 & highschool[_N]==9999

. sort id year

. list

     +----------------------------------------------------------+
     |  id   state   year   gr   primar~l   middle~l   highsc~l |
     |----------------------------------------------------------|
  1. | 001       0      1    0          1          0          0 |
  2. | 001       0      2    1          1          0          0 |
  3. | 001       0      3    3          1          0          0 |
  4. | 001       1      4    2          1          0          0 |
  5. | 002       0      1    0          1          0          0 |
     |----------------------------------------------------------|
  6. | 002       0      2    1          1          0          0 |
  7. | 002       0      3    2          1          0          0 |
  8. | 002       0      4    4          1          0          0 |
  9. | 002       1      5    3          1          0          0 |
 10. | 002       0      6    6          0          1          0 |
     |----------------------------------------------------------|
 11. | 002       1      7    5          0          1          0 |
 12. | 002       1      8    6          0          1          0 |
 13. | 003       0      1    0          1          0          0 |
 14. | 003       0      2    1          1          0          0 |
 15. | 003       0      3    2          1          0          0 |
     |----------------------------------------------------------|
 16. | 003       0      4    3          1          0          0 |
 17. | 003       0      5    4          1          0          0 |
 18. | 003       .      6    .          0          1          0 |
 19. | 003       0      7    7          0          1          0 |
 20. | 003       1      8    6          0          1          0 |
     |----------------------------------------------------------|
 21. | 003       0      9    8          0          0       9999 |
 22. | 003       .     10    .          0          0       9999 |
 23. | 003       0     11   10          0          0       9999 |
 24. | 003       0     12   11          0          0       9999 |
 25. | 004       0      1    0       9999          0          0 |
     |----------------------------------------------------------|
 26. | 004       .      2    .       9999          0          0 |
 27. | 004       0      3    2       9999          0          0 |
     +----------------------------------------------------------+

.

[smith Jason]

Thank you, Carol!
However, I applied your code to the simulated dataset below, I can't get what I want. May I bother you to take a look when you have time?

clear
input str10 id byte (state year grade)
001 0 1 0
001 0 2 1
001 0 3 2
001 0 4 3
001 0 5 4
001 0 6 5
001 0 7 6
001 0 8 7
001 0 9 8
001 0 10 9
001 0 11 10
001 0 12 11
001 0 13 12
002 0 1 0
002 0 2 1
002 . 3 .
002 0 4 3
002 1 5 2
002 1 6 3
002 0 7 4
002 0 8 5
002 . 9 .
002 0 10 7
002 0 11 8
002 0 12 9
002 0 13 10
003 0 1 0
003 0 2 1
003 1 3 1
003 . 4 .
003 0 5 3
003 0 6 4
003 . 7 .
003 0 8 6
003 0 9 7
003 1 10 7
003 0 11 8
003 0 12 9
003 0 13 10
004 0 1 0
004 0 2 1
004 0 3 2
004 0 4 3
004 0 5 4
004 0 6 5
004 0 7 6
004 0 8 7
004 0 9 8
004 0 10 9
004 . 11 .
004 1 12 8
004 1 13 9
005 0 1 0
005 0 2 1
005 1 3 1
005 0 4 3
005 0 5 4
005 . 6 .
005 0 7 6
005 . 8 .
005 1 9 6
005 0 10 8
005 0 11 10
005 0 12 11
005 1 13 11
end

I just replaced year with "grade" in your code without additional changes.

bys id: gen primary=1 if grade<=5 &! missing (id, state, year)
bys id: replace primary=. if grade<=5 & missing (id, state, year)
bys id (primary): replace primary=. if primary[_N] ==.
bys id: replace primary=0 if primary==.
bys id: gen middle=1 if grade>=6 & grade<=8 &! missing (id, state, year)
by id: replace middle=1 if grade>=6 & grade<=8 & missing (id, state, year)
bys id: replace middle=0 if middle==.
bys id: gen high=1 if grade>=9 & year<=12 &! missing (id, state, year).
bys id: replace grade=. if grade>=9 & grade<=12 & missing (id, state, year).
replace high=0 if high==.
bys id (high): replace high=. if grade>=9 & grade<=12 & high[_N] ==.
sort id year