Recode variable based on previous value

[Leon Edelman]

Ciao, Below is sample data.

clear
input STUDENT GRADELEVEL STATUS WANT
1 10 0 0
1 10 0 0
1 10 1 1
1 10 0 1
2 9 1 1
2 9 1 1
2 9 0 1
2 9 2 2
3 11 1 1
3 11 0 1
3 11 2 2
3 11 1 2
4 8 2 2
4 8 0 2
4 8 0 2
4 8 0 2
end

I have the STUDENT, GRADELEVEL, STATUS columns. I hope to create the WANT column. As shown, the WANT column depends on the STATUS column and updates. STATUS takes on the maximum value of WANT when it occurs and then fills down the rest of the way. Basically 0 = did not take test; 1 = failed test; 2 = passed test. There are some clerical data entry errors. So ultimately I want to carry forward the 2 when it occurs and the 1 when it occurs; but 2 is the ultimate carry forward goal.

[William Lisowski]

Thank you for the clear explanation with sample data. I think the following does what you want.

generate want2 = STATUS
bysort STUDENT (GRADELEVEL): replace want2 = max(want2,want2[_n-1])

[Leon Edelman]

Thank you for the clear explanation with sample data. I think the following does what you want.

generate want2 = STATUS
bysort STUDENT (GRADELEVEL): replace want2 = max(want2,want2[_n-1])

William Lisowski thanks a bunch. Now say a 'PASS' (STATUS = 2) only counts if a STUDENT gets STATUS = 2 and then the next row for that STUDENT STATUS = 2 or STATUS = 0. Or a student gets a STATUS = 1 only if they get 1 twice in a row or a 1 and a 0. As below for WANT2 (PASS and FAIL) and WANT3 (only PASS):

 
STUDENT
GRADELEVEL
STATUS
WANT2
WANT3

5
10
0
0
0

5
10
2
0
0

5
10
2
2
2

5
10
0
2
2

6
8
2
0
0

6
8
0
2
2

6
8
1
2
2

6
8
2
2
2

7
11
2
0
0

7
11
1
0
2

7
11
1
1
2

7
11
1
1
2

8
9
1
0
0

8
9
1
1
0

8
9
2
1
0

8
9
1
2
2

9
8
1
0
0

9
8
1
1
0

9
8
2
1
0

9
8
0
2
2

[David Benson]

Leon, I've pasted your data from #3 below. Could you confirm that the below is what you want (especially for want2). Based on your description in #3, it's not clear to me why want2==2 in obs 7 , 8, or 16. Also, once a student obtains want2==2, can want2 ever equal 1 or 0 after that? (Same question for want3).

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(student gradelevel status want2 want3)
5 10 0 0 0
5 10 2 0 0
5 10 2 2 2
5 10 0 2 2
6  8 2 0 0
6  8 0 2 2
6  8 1 2 2
6  8 2 2 2
7 11 2 0 0
7 11 1 0 2
7 11 1 1 2
7 11 1 1 2
8  9 1 0 0
8  9 1 1 0
8  9 2 1 0
8  9 1 2 2
9  8 1 0 0
9  8 1 1 0
9  8 2 1 0
9  8 0 2 2
end

* Obs I have questions on I've colored red
list, sepby(student) abbrev(12)

     +-----------------------------------------------+
     | student   gradelevel   status   want2   want3 |
     |-----------------------------------------------|
  1. |       5           10        0       0       0 |
  2. |       5           10        2       0       0 |
  3. |       5           10        2       2       2 |
  4. |       5           10        0       2       2 |
     |-----------------------------------------------|
  5. |       6            8        2       0       0 |
  6. |       6            8        0       2       2 |
  7. |       6            8        1       2       2 |
  8. |       6            8        2       2       2 |
     |-----------------------------------------------|
  9. |       7           11        2       0       0 |
 10. |       7           11        1       0       2 |
 11. |       7           11        1       1       2 |
 12. |       7           11        1       1       2 |
     |-----------------------------------------------|
 13. |       8            9        1       0       0 |
 14. |       8            9        1       1       0 |
 15. |       8            9        2       1       0 |
 16. |       8            9        1       2       2 |
     |-----------------------------------------------|
 17. |       9            8        1       0       0 |
 18. |       9            8        1       1       0 |
 19. |       9            8        2       1       0 |
 20. |       9            8        0       2       2 |
     +-----------------------------------------------+

[Leon Edelman]

Leon, I've pasted your data from #3 below. Could you confirm that the below is what you want (especially for want2). Based on your description in #3, it's not clear to me why want2==2 in obs 7 , 8, or 16. Also, once a student obtains want2==2, can want2 ever equal 1 or 0 after that? (Same question for want3).

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(student gradelevel status want2 want3)
5 10 0 0 0
5 10 2 0 0
5 10 2 2 2
5 10 0 2 2
6 8 2 0 0
6 8 0 2 2
6 8 1 2 2
6 8 2 2 2
7 11 2 0 0
7 11 1 0 2
7 11 1 1 2
7 11 1 1 2
8 9 1 0 0
8 9 1 1 0
8 9 2 1 0
8 9 1 2 2
9 8 1 0 0
9 8 1 1 0
9 8 2 1 0
9 8 0 2 2
end

* Obs I have questions on I've colored red
list, sepby(student) abbrev(12)

+-----------------------------------------------+
| student gradelevel status want2 want3 |
|-----------------------------------------------|
1. | 5 10 0 0 0 |
2. | 5 10 2 0 0 |
3. | 5 10 2 2 2 |
4. | 5 10 0 2 2 |
|-----------------------------------------------|
5. | 6 8 2 0 0 |
6. | 6 8 0 2 2 |
7. | 6 8 1 2 2 |
8. | 6 8 2 2 2 |
|-----------------------------------------------|
9. | 7 11 2 0 0 |
10. | 7 11 1 0 2 |
11. | 7 11 1 1 2 |
12. | 7 11 1 1 2 |
|-----------------------------------------------|
13. | 8 9 1 0 0 |
14. | 8 9 1 1 0 |
15. | 8 9 2 1 0 |
16. | 8 9 1 2 2 |
|-----------------------------------------------|
17. | 9 8 1 0 0 |
18. | 9 8 1 1 0 |
19. | 9 8 2 1 0 |
20. | 9 8 0 2 2 |
+-----------------------------------------------+

David Benson that is exactly perfect. The highlighted red numbers are exactly what I am attempting to get.

[David Benson]

Sorry, then the decision rules for want2 and want3 are not clear to me (if I'm the only one, that's fine :-). But I thought for want3 they had to get a 2,2 (2 twice in a row) or 2,0. And for want2 they got a 1,1 or 1,0. So I am not sure why want2==2 in obs 7 , 8, or 16.

[Leon Edelman]

David Benson Benson;n1477044]Sorry, then the decision rules for want2 and want3 are not clear to me (if I'm the only one, that's fine :-). But I thought for want3 they had to get a 2,2 (2 twice in a row) or 2,0. And for want2 they got a 1,1 or 1,0. So I am not sure why want2==2 in obs 7 , 8, or 16.[/QUOTE]

@ David Benson thanks so much. Your assistance would be much appreciated. Here is the decision rule actually:

want2:

2, 0 = 2
2, 1 = 2
1, 0 = 1
1, 1 = 1

want3:
2,0 = 2
2,1 = 2
1, 0 = 0
1, 1 = 1

Does this provide clarification?

7, 8, 16 are equals to 2 because once a student has a 2 they always have a 2. similarly once a student has a 1 they always have a 1, unless they get a 2 based on the decision rules described herin.

[David Benson]

So, try this. This is based on the data from post #4.

by student: gen n = _n  // just creating a counter
by student: gen last2 = string(status[_n-1]) + string(status)  // this is the key
// This is the "2,0" or "1,1" from above. I just didn't add the comma because it made it easier
label var last2 "String of status[_n-1] + status[1]"

gen want2=0
replace want2 = 1 if inlist( last2, "10", "11")  // again, this is how I implemented the "1, 0" = 1. I just didn't add a comma.
replace want2 = 2 if inlist( last2, "20", "22")  // could make inlist( last2, "20", "21", "22")
by student: replace want2 = 1 if want2==0 & want2[_n-1]==1  // once a 1, cont as 1 (unless becomes a 2)
by student: replace want2 = 2 if want2[_n-1]==2  // once a 2, always a 2

gen want3 = 0
replace want3 = 1 if last2=="11"
replace want3 = 2 if inlist(last2, "20", "21", "22")  // this is based on "2,0" = 2, "2,1" = 2 (and presumably "2,2" = 2 as well)
// I don't actually see a 1 in want3, even though based on these rules obs #14, 15, 18, & 19 would be ==1
by student: replace want3 = 2 if want3[_n-1]==2  // once a 2, always a 2

// orig2 & orig3 are what you listed that you wanted in want2 want3 from post #5
// I posted them here to make it easy to compare (obs that are different are in red)
list student gradelevel status last2 want2 want3 sp orig2 orig3, sepby(student)

     +--------------------------------------------------------------------------+
     | student   gradel~l   status   last2   want2   want3   sp   orig2   orig3 |
     |--------------------------------------------------------------------------|
  1. |       5         10        0      .0       0       0            0       0 |
  2. |       5         10        2      02       0       0            0       0 |
  3. |       5         10        2      22       2       2            2       2 |
  4. |       5         10        0      20       2       2            2       2 |
     |--------------------------------------------------------------------------|
  5. |       6          8        2      .2       0       0            0       0 |
  6. |       6          8        0      20       2       2            2       2 |
  7. |       6          8        1      01       2       2            2       2 |
  8. |       6          8        2      12       2       2            2       2 |
     |--------------------------------------------------------------------------|
  9. |       7         11        2      .2       0       0            0       0 |
 10. |       7         11        1      21       0       2            0       2 |
 11. |       7         11        1      11       1       2            1       2 |
 12. |       7         11        1      11       1       2            1       2 |
     |--------------------------------------------------------------------------|
 13. |       8          9        1      .1       0       0            0       0 |
 14. |       8          9        1      11       1       1            1       0 |
 15. |       8          9        2      12       1       0            1       0 |
 16. |       8          9        1      21       1       2            2       2 |
     |--------------------------------------------------------------------------|
 17. |       9          8        1      .1       0       0            0       0 |
 18. |       9          8        1      11       1       1            1       0 |
 19. |       9          8        2      12       1       0            1       0 |
 20. |       9          8        0      20       2       2            2       2 |
     +--------------------------------------------------------------------------+

Note that obs 14, 16, and 18 are different. In obs #14, want3==1 because in your decision rule in the prior post you said 1,1 = 1. From the rule "once a student has a 1 they always have a 1" want3 should ==1 in obs #15 (the same situation exists for #18 & 19).

For #16, want2 should==2 because in your prior post you said 2,1 = 2. The only reason I didn't do that is that in your verbal explanation in post #3 you said "(STATUS = 2) only counts if a STUDENT gets STATUS = 2 and then the next row for that STUDENT STATUS = 2 or STATUS = 0." (Which would imply 2,2 or 2,0 but preclude 2,1). Also, note that obs #10 is a 2,1 which would mean want2==2 for it and then for obs #11 and 12 based on the "once a 2, always a 2" rule.

Obviously I'm indifferent to it, I just thought I would point it out. You have the code above to do it either way.

Hope that helps!
--David