Assign sequencenumber

[Chris_LK_87]

Hello, 

I have a dataset with hospitaladmissions for patients with the dates for enrollment and discharge across medical units. Patients can be moved around different units resulting in new lines in my dataset.  A patient can have multiple admissions.  When the patient finally is discharged from the hospital the variable episode has 'Yes' (the startdate for the first entry and enddate admission). I would like to give each episode a Seq_ID as shown in data want: 

data have;

;

input id$ inperiod:yymmdd10. outperiod:yymmdd10. episode$;

format inperiod yymmddd10. outperiod yymmddd10.;

datalines;

1 2021-05-09 2021-05-11 No

1 2021-05-11 2021-05-15 No

1 2021-05-15 2021-05-17 No

1 2021-05-09 2021-05-17 Yes

1 2022-01-11 2022-01-20 No

1 2022-01-20 2022-01-22 No

1 2022-01-11 2022-01-22 Yes

2 2020-04-09 2020-04-11 No

2 2020-04-11 2020-04-17 No

2 2020-04-09 2020-04-17 Yes

2 2022-07-20 2022-07-20 No

2 2022-07-20 2022-07-22 No

2 2022-07-20 2022-07-22 Yes

3 2022-03-01 2022-03-03 No

3 2022-03-03 2022-03-31 No

3 2022-03-01 2022-03-31 Yes

4 2022-05-25 2022-05-28 No

4 2022-05-28 2022-06-02 No

4 2022-05-25 2022-06-02 Yes

;

run;

data want;

input id$ inperiod:yymmdd10. outperiod:yymmdd10. episode$ seq_id

;

1 2021-05-09 2021-05-11 No 1

1 2021-05-11 2021-05-15 No 1

1 2021-05-15 2021-05-17 No 1

1 2021-05-09 2021-05-17 Yes 1

1 2022-01-11 2022-01-20 No 2

1 2022-01-20 2022-01-22 No 2

1 2022-01-11 2022-01-22 Yes 2

2 2020-04-09 2020-04-11 No 3

2 2020-04-11 2020-04-17 No 3

2 2020-04-09 2020-04-17 Yes 3

2 2022-07-20 2022-07-20 No 4

2 2022-07-20 2022-07-22 No 4

2 2022-07-20 2022-07-22 Yes 4

3 2022-03-01 2022-03-03 No 5

3 2022-03-03 2022-03-31 No 5

3 2022-03-01 2022-03-31 Yes 5

4 2022-05-25 2022-05-28 No 6

4 2022-05-28 2022-06-02 No 6

4 2022-05-25 2022-06-02 Yes 6

;

run;

[PeterClemmensen · Accepted solution]

Try this

data want;
   set have;
   by ID episode notsorted;
   if first.episode and episode = 'No' then seq_id + 1;
run;

Result:

id inperiod   outperiod  episode seq_id
1  2021-05-09 2021-05-11 No      1
1  2021-05-11 2021-05-15 No      1
1  2021-05-15 2021-05-17 No      1
1  2021-05-09 2021-05-17 Yes     1
1  2022-01-11 2022-01-20 No      2
1  2022-01-20 2022-01-22 No      2
1  2022-01-11 2022-01-22 Yes     2
2  2020-04-09 2020-04-11 No      3
2  2020-04-11 2020-04-17 No      3
2  2020-04-09 2020-04-17 Yes     3
2  2022-07-20 2022-07-20 No      4
2  2022-07-20 2022-07-22 No      4
2  2022-07-20 2022-07-22 Yes     4
3  2022-03-01 2022-03-03 No      5
3  2022-03-03 2022-03-31 No      5
3  2022-03-01 2022-03-31 Yes     5
4  2022-05-25 2022-05-28 No      6
4  2022-05-28 2022-06-02 No      6
4  2022-05-25 2022-06-02 Yes     6