expand data?

[River Huang]

Dear All, Suppose that I have this data set

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id event all)
1 1 4
2 3 5
3 2 6
3 4 6
end

There are three individuals, id=1,2,3. Each has an insurance contract for `all' years. In the `event' year, they have accidents. I expand the data by

expand po_prem_year+1
sort policy_no event_po_year
by policy_no: gen t = _n-1

and obtain

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(policy_no event_po_year po_prem_year) float t
1 1 4  0
1 1 4  1
1 1 4  2
1 1 4  3
1 1 4  4
2 3 5  0
2 3 5  1
2 3 5  2
2 3 5  3
2 3 5  4
2 3 5  5
3 2 6  0
3 2 6  1
3 2 6  2
3 2 6  3
3 2 6  4
3 2 6  5
3 2 6  6
3 4 6  7
3 4 6  8
3 4 6  9
3 4 6 10
3 4 6 11
3 4 6 12
3 4 6 13
end

However, I wish to have a final data set like

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id wanted all) float t
1 0 4  0
1 1 4  1
1 0 4  2
1 0 4  3
1 0 4  4
2 0 5  0
2 0 5  1
2 0 5  2
2 1 5  3
2 0 5  4
2 0 5  5
3 0 6  0
3 0 6  1
3 1 6  2
3 0 6  3
3 1 6  4
3 0 6  5
3 0 6  6
end

Given this expanded (unbalanced) panel data, I'd like to have the `wanted' to be 1 if there is an accident in the `t' year. Any suggestions? Thanks.

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id event all)
1 1 4
2 3 5
3 2 6
3 4 6
end

rename event t
frame put id all, into(wanted)
frame wanted{
    contract id all, freq(t)
    expand all+1
    bys id: replace t=_n-1
    frlink 1:1 id t, frame(default)
    gen wanted= !missing(default)
    drop default
    list, sepby(id)
}

Res.:

.     list, sepby(id)

     +-----------------------+
     | id   all   t   wanted |
     |-----------------------|
  1. |  1     4   0        0 |
  2. |  1     4   1        1 |
  3. |  1     4   2        0 |
  4. |  1     4   3        0 |
  5. |  1     4   4        0 |
     |-----------------------|
  6. |  2     5   0        0 |
  7. |  2     5   1        0 |
  8. |  2     5   2        0 |
  9. |  2     5   3        1 |
 10. |  2     5   4        0 |
 11. |  2     5   5        0 |
     |-----------------------|
 12. |  3     6   0        0 |
 13. |  3     6   1        0 |
 14. |  3     6   2        1 |
 15. |  3     6   3        0 |
 16. |  3     6   4        1 |
 17. |  3     6   5        0 |
 18. |  3     6   6        0 |
     +-----------------------+

[River Huang]

Dear Andrew, It works just fine, Thanks a lot.

[Romalpa Akzo]

expand all + 1

bys id event: gen t = _n - 1
gen wanted = event == t

collapse (max) all wanted, by(id t)

[River Huang]

Dear Romalpa, Many thanks for this interesting and concise suggestion.