Count under a specified condition

[Steven Vaccato]

Hello to all,

I need your help with an issue I am dealing with. I am currently working with a database in panel format that has the administrative records of the social security. Specifically, I have a unique identifier for each individual affiliated to social security and a dummy variable (affiliation) that indicates if that month I contribute or not. I am working with a period that goes from 2019 to 2022 on a monthly basis. So I create a variable (time) that indicates the number of the month in the respective year, i.e. January 2019 will be 1 and December 2022 will be 48. Finally, I need to get the count of reentries existing in each month. A reentry I consider it as having 1 in the variable affiliation but with the condition that in the immediately previous month it had 0 but that in some month before that it already had 1. With this logic I create a variable (reentries) under this syntax:

gen lag = l1.affiliation
gen diff = lag - affiliation
gen reentry = 0 if dif != .
replace reentry = 1 if dif==-1

However, there are cases where their first affiliation also takes the value of 1 and in this case that would not be a reentry so I am overcounting the reentries. I need to solve this so that I only count the reentries or if there is another way it would help me a lot.

I tried to explain it as clearly as possible. Please help me with any ideas on how to get this count. I would really appreciate any comments.

[Clyde Schechter]

Untested, because no example data was provided. But I believe this will do it:

//  VERIFY affiliation IS ALWAYS 0 OR 1
assert inlist(affiliation, 0, 1)
//  AND THAT THERE ARE NO GAPS IN THE TIME SEQUENCE
xtset person_id month
assert `r(gaps)' == 0

by person_id (month): gen int run = sum(affiliation != L1.affiliation)
gen byte re_entry = (affiliation == 1) & run > 2
by person_id run (month), sort: replace re_entry = 0 if _n > 1

[Steven Vaccato]

I appreciate your answer, Clyde. The syntax you suggested worked perfectly.