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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.