Generting a variable that counts consecutive observations within person

[Jake Magel]

Hello, I am trying to identify when an individual has at least15 consecutive months of insurance eligibility. diff_more1mo == 1 indicates that there were no breaks in eligibility between that month and the month before. A 0 indicates that there is a break (example highlighted in bold and italics below) and "." indicates a change from 1 member to the next (Unique_Member_id). "." is also the first month of full eligibility so it is possible that "." could be the 1st value in the consecutive list of more than 15 1s. How do I create a new variable where an individual has 15 or more consecutive months of eligibility (again, which may include a "." as the first month.

I sure appreciate your help with this.


clear
input long Unique_Member_id float(eligibility_date2 eligdate_dif diff_more1mo eligibility_order)
1 19359 . . 1
1 19390 31 1 2
1 19418 28 1 3
1 19449 31 1 4
1 19479 30 1 5
1 19510 31 1 6
1 19540 30 1 7
1 19571 31 1 8
1 19602 31 1 9
1 19632 30 1 10
1 19663 31 1 11
1 19693 30 1 12
1 19724 31 1 13
1 19755 31 1 14
1 19783 28 1 15
1 19814 31 1 16
1 19844 30 1 17
1 19875 31 1 18
1 19905 30 1 19
1 19936 31 1 20
1 19967 31 1 21
1 19997 30 1 22
1 20028 31 1 23
2 20182 . . 1
2 20212 30 1 2
2 20243 31 1 3
2 20273 30 1 4
2 20304 31 1 5
2 20335 31 1 6
2 20365 30 1 7
2 20396 31 1 8
2 20426 30 1 9
3 19356 . . 1
3 19387 31 1 2
3 19415 28 1 3
3 19446 31 1 4
3 19476 30 1 5
3 19507 31 1 6
3 19537 30 1 7
3 19568 31 1 8
3 19599 31 1 9
3 19629 30 1 10
3 19660 31 1 11
3 19690 30 1 12
3 19721 31 1 13
3 19752 31 1 14
3 19780 28 1 15
3 19811 31 1 16
3 19841 30 1 17
3 19872 31 1 18
3 19902 30 1 19
3 19933 31 1 20
3 19964 31 1 21
3 19994 30 1 22
3 20025 31 1 23
3 20055 30 1 24
3 20086 31 1 25
3 20117 31 1 26
3 20145 28 1 27
3 20176 31 1 28
3 20206 30 1 29
3 20237 31 1 30
3 20267 30 1 31
3 20298 31 1 32
3 20329 31 1 33
3 20359 30 1 34
3 20390 31 1 35
3 20420 30 1 36
3 20451 31 1 37
3 20482 31 1 38
3 20511 29 1 39
3 20542 31 1 40
3 20572 30 1 41
3 20603 31 1 42
4 20396 . . 1
4 20426 30 1 2
4 20457 31 1 3
4 20488 31 1 4
4 20517 29 1 5
4 20548 31 1 6
4 20578 30 1 7
4 20609 31 1 8
5 19933 . . 1
5 19964 31 1 2
5 19994 30 1 3
5 20025 31 1 4
5 20055 30 1 5
5 20086 31 1 6
5 20117 31 1 7
5 20145 28 1 8
5 20176 31 1 9
5 20206 30 1 10
5 20237 31 1 11
5 20298 61 0 12
5 20329 31 1 13
5 20359 30 1 14
5 20390 31 1 15
5 20420 30 1 16
5 20451 31 1 17
5 20482 31 1 18
end
format %td eligibility_date2

[Giovanni Colitti]

If I understand the problem correctly, it seems to me you've already done most of the work. I just replaced diff_more1mo to 1 for the user change observations. It looks as though you account for dec. to jan. consecutive month changes with diff_more1mo, but I include code that will account for this as well. Once you have an indicator for whether an observations is in a consecutive sequence, you just need to sum this indicator by id and, if you like, generate a dummy for whether or not the sum of consecutive months is greater than or equal to 15.

qui {
clear
input long Unique_Member_id float(eligibility_date2 eligdate_dif diff_more1mo eligibility_order)
1 19359 . . 1
1 19390 31 1 2
1 19418 28 1 3
1 19449 31 1 4
1 19479 30 1 5
1 19510 31 1 6
1 19540 30 1 7
1 19571 31 1 8
1 19602 31 1 9
1 19632 30 1 10
1 19663 31 1 11
1 19693 30 1 12
1 19724 31 1 13
1 19755 31 1 14
1 19783 28 1 15
1 19814 31 1 16
1 19844 30 1 17
1 19875 31 1 18
1 19905 30 1 19
1 19936 31 1 20
1 19967 31 1 21
1 19997 30 1 22
1 20028 31 1 23
2 20182 . . 1
2 20212 30 1 2
2 20243 31 1 3
2 20273 30 1 4
2 20304 31 1 5
2 20335 31 1 6
2 20365 30 1 7
2 20396 31 1 8
2 20426 30 1 9
3 19356 . . 1
3 19387 31 1 2
3 19415 28 1 3
3 19446 31 1 4
3 19476 30 1 5
3 19507 31 1 6
3 19537 30 1 7
3 19568 31 1 8
3 19599 31 1 9
3 19629 30 1 10
3 19660 31 1 11
3 19690 30 1 12
3 19721 31 1 13
3 19752 31 1 14
3 19780 28 1 15
3 19811 31 1 16
3 19841 30 1 17
3 19872 31 1 18
3 19902 30 1 19
3 19933 31 1 20
3 19964 31 1 21
3 19994 30 1 22
3 20025 31 1 23
3 20055 30 1 24
3 20086 31 1 25
3 20117 31 1 26
3 20145 28 1 27
3 20176 31 1 28
3 20206 30 1 29
3 20237 31 1 30
3 20267 30 1 31
3 20298 31 1 32
3 20329 31 1 33
3 20359 30 1 34
3 20390 31 1 35
3 20420 30 1 36
3 20451 31 1 37
3 20482 31 1 38
3 20511 29 1 39
3 20542 31 1 40
3 20572 30 1 41
3 20603 31 1 42
4 20396 . . 1
4 20426 30 1 2
4 20457 31 1 3
4 20488 31 1 4
4 20517 29 1 5
4 20548 31 1 6
4 20578 30 1 7
4 20609 31 1 8
5 19933 . . 1
5 19964 31 1 2
5 19994 30 1 3
5 20025 31 1 4
5 20055 30 1 5
5 20086 31 1 6
5 20117 31 1 7
5 20145 28 1 8
5 20176 31 1 9
5 20206 30 1 10
5 20237 31 1 11
5 20298 61 0 12
5 20329 31 1 13
5 20359 30 1 14
5 20390 31 1 15
5 20420 30 1 16
5 20451 31 1 17
5 20482 31 1 18
end
format %td eligibility_date2
}

gen month = month(eligibility_date2)
gen year = year(eligibility_date2)

// Replace diff_more1mo for member id change obs
bysort Unique_Member_id : replace diff_more1mo = 1 if month[_n+1] - month == 1 & year[_n+1] == year[_n]

// Replace diff_more1mo for dec to jan within id
bysort Unique_Member_id : replace diff_more1mo = 1 if month[_n+1] == 1 & month == 12 & year[_n+1] == 1+ year[_n] 

// Count number of consecutive months by id
bysort Unique_Member_id : egen SumConsecMonths = sum(diff_more1mo)

// Gen dummy for whether consecutive months >= 15
gen Consec15 = (SumConsecMonths >=15)

[Jake Magel]

Giovann, thanks so much! Works like a charm.
jm