Dear Forum members,

I am working on a panel dataset that contains more than 50,000 hospital records. I would like to generate a count variable (so i can do a poisson regression) that will sum the number of infections that an individual has had during their lifetime.

In this case, I would like to use the Spell method using specific criteria to assign individuals with multiple episodes that are within 28 days of each other, a single event.

Briefly, a hospital inpatient “spell” is often defined as a total continuous stay of a patient from admission to discharge.

A spell is split into one or more “episodes”, each of which is a continuous period under one or more consultants/clinicians. For example, when a patient is admitted to the hospital and sees a first consultant/clinician, they get assigned a first episode with an admission date. If a patient is transferred to another consultant during a spell, a new episode is generated (with admission and discharge date). In the case of infection, it is likely that a person with multiple episodes who is seen and assigned different diagnoses for infection represents one event (one infection).

Below is an overview of the data
PID admis_date disch_date ICD diagnosis
1 22-Mar-96 26-Mar-96 infection 1
1 26-Mar-96 02-Apr-96 infection 2
1 02-Apr-96 15-Apr-96 infection 3
1 20-Apr-96 18-Apr-96 infection 2
1 08-Nov-01 12-Nov-01 infection
1 04-Sep-07 06-Sep-07 infection
1 24-Nov-12 28-Nov-12 infection
1 16-Nov-15 20-Nov-15 infection
2 13-May-01 17-May-01 infection 1
2 17-May-01 21-May-01 infection 2
2 01-Oct-03 01-Oct-03 infection
2 20-Dec-08 03-Jan-09 infection
I would like to create a table like the one below assigning infections that occur within 28 days of each other as one infection (event). From there i would like to create a count variable with the number of infections that an individual has had in total. Highlighted in red are the columns I would like to create.

Does anyone have an idea how i can go about solving this?
PID admis_date disch_date ICD diagnosis days between
first and last admission episode
(<=28 days)		 infection
admis_date		 infection
disch_date		 no. of Infections total infection count
1 23-Mar-96 26-Mar-96 infection 1 5
1 26-Mar-96 02-Apr-96 infection 2 5
1 02-Apr-96 20-Apr-96 infection 3 5
1 20-Apr-96 23-Apr-96 infection 2 28 20-Apr-96 23-Apr-96 1 5
1 08-Nov-01 12-Nov-01 infection 08-Nov-01 12-Nov-01 2 5
1 04-Sep-07 06-Sep-07 infection 04-Sep-07 06-Sep-07 3 5
1 24-Nov-12 28-Nov-12 infection 24-Nov-12 28-Nov-12 4 5
1 16-Nov-15 20-Nov-15 infection 16-Nov-15 20-Nov-15 5 5
2 13-May-01 17-May-01 infection 1 3
2 17-May-01 21-May-01 infection 2 4 17-May-01 21-May-01 1 3
2 01-Oct-03 01-Oct-03 infection 01-Oct-03 03-Oct-03 2 3
2 20-Dec-08 03-Jan-09 infection 30-Dec-08 03-Dec-09 3 3