Hi I have the dataset as shown below
| PTNUM | WAVE NO | AGE | STATE |
|---|---|---|---|
| 1 | 1 | 18 | 1 |
| 1 | 2 | NA | NA |
| 1 | 3 | NA | NA |
| 1 | 4 | 29 | 1 |
| 1 | 5 | NA | NA |
| 2 | 1 | 18 | 1 |
| 2 | 2 | 19 | 2 |
| 2 | 3 | 25 | 2 |
| 2 | 4 | 31 | NA |
| 2 | 5 | 39 | 3 |
| 3 | 1 | 19 | 1 |
| 3 | 2 | 21 | 1 |
| 3 | 3 | NA | NA |
| 3 | 4 | 29 | 2 |
| 3 | 5 | 35 | 3 |
| 4 | 1 | 18 | 1 |
| 4 | 2 | 19 | 1 |
| 4 | 3 | 25 | 2 |
| 4 | 4 | 29 | 3 |
| 4 | 5 | 33 | 3 |
| 5 | 1 | 16 | 1 |
| 5 | 2 | 18 | 2 |
| 5 | 3 | 24 | 3 |
| 5 | 4 | 30 | 2 |
| 5 | 5 | 36 | 3 |
I want to arrange my data based on the transitions of the states between the waves which would look like the data shown below. So the subjects may move between these transitions (1->2), (2->3), (3->2). If the subjects have missing data i.e., NA that should reflect in the data. And I also want to create a censoring variable, the censoring variable stays 0 until the subject reaches state 2, once the subject reaches state 2 the censoring variable becomes 1 and never returns to 0.
Expected dataset
| PTNUM | AGE 1 | AGE 2 | TRANSI 1 | TRANSI 2 | CENSOR | WAVE |
|---|---|---|---|---|---|---|
| 1 | 18 | NA | 1 | NA | 0 | 1-2 |
| 1 | NA | NA | NA | NA | 0 | 2-3 |
| 1 | NA | 29 | NA | 1 | 0 | 3-4 |
| 1 | 29 | NA | 1 | NA | 0 | 4-5 |
| 2 | 18 | 19 | 1 | 2 | 1 | 1-2 |
| 2 | 19 | 25 | 2 | 2 | 1 | 2-3 |
| 2 | 25 | 31 | 2 | NA | 1 | 3-4 |
| 2 | 31 | 39 | NA | 3 | 1 | 4-5 |
| 3 | 19 | 21 | 1 | 1 | 0 | 1-2 |
| 3 | 21 | NA | 1 | NA | 0 | 2-3 |
| 3 | NA | 29 | NA | 2 | 1 | 3-4 |
| 3 | 29 | 35 | 2 | 3 | 1 | 4-5 |
| 4 | 18 | 19 | 1 | 1 | 0 | 1-2 |
| 4 | 19 | 25 | 1 | 2 | 1 | 2-3 |
| 4 | 25 | 29 | 2 | 3 | 1 | 3-4 |
| 4 | 29 | 33 | 3 | 3 | 1 | 4-5 |
| 5 | 16 | 18 | 1 | 2 | 1 | 1-2 |
| 5 | 18 | 24 | 2 | 3 | 1 | 2-3 |
| 5 | 24 | 30 | 3 | 2 | 1 | 3-4 |
| 5 | 30 | 36 | 2 | 3 | 1 | 4-5 |
Can please someone help me with this? Thank you.
