topic Re: creating episodes using multiple dates when not clean sorted in SAS Programming

creating episodes using multiple dates when not clean sorted

indrakshiroy90 — Fri, 21 Mar 2025 06:13:43 GMT

Hello,

I have a dataset with multiple rows per id and I am trying to create episode start and end dates. My data looks as follows:

ID	trgt_dt	A1600	A1700	A1800	R4	A2100
1	9-Jun-00	.	.	.	.	.
1	14-Jun-00	2-Jun-00	1	3	.	.
1	22-Jun-00	.	.	.	22-Jun-00	1
1	13-Nov-03	.	.	.	.	.
1	19-Nov-03	6-Nov-03	1	3	.	.
1	22-Nov-03	.	.	.	22-Nov-03	1
1	23-Jan-07	16-Jan-07	1	3	.	.
1	29-Jan-07	.	.	.	.	.
1	31-Jan-07	.	.	.	31-Jan-07	1
1	1-Feb-07	.	.	.	.	.
1	11-Feb-07	31-Jan-07	1	3	11-Feb-07	3
1	12-Feb-07	12-Feb-07	2	3	.	.
1	19-Feb-07	6-Feb-07	1	3	.	.
1	22-Feb-07	.	.	.	.	.
1	5-Mar-07	.	.	.	.	.
1	26-Mar-07	.	.	.	26-Mar-07	1

A1600 is the entry/reentry date, A1700 = 1 when its an entry and 2 when its a reentry. R4 is the discharge date. I want to use the dates per ID to create a chronological sequence of events for the patient in terms of start and end of episodes. Sometimes discharge dates can be missing. The desired output would look as follows:

ID	trgt_dt	A1600	R4	stay_entry	stay_end
1	9-Jun-00	.	.	2-Jun-00	.
1	14-Jun-00	2-Jun-00	.	2-Jun-00	.
1	22-Jun-00	.	22-Jun-00	2-Jun-00	22-Jun-00
1	13-Nov-03	.	.	6-Nov-03	.
1	19-Nov-03	6-Nov-03	.	6-Nov-03	.
1	22-Nov-03	.	22-Nov-03	6-Nov-03	22-Nov-03
1	23-Jan-07	16-Jan-07	.	16-Jan-07	.
1	29-Jan-07	.	.	16-Jan-07	.
1	31-Jan-07	.	31-Jan-07	16-Jan-07	31-Jan-07
1	1-Feb-07	.	1-Feb-07	31-Jan-07	1-Feb-07
1	11-Feb-07	31-Jan-07	11-Feb-07	6-Feb-07	11-Feb-07
1	12-Feb-07	12-Feb-07	.	12-Feb-07	.
1	19-Feb-07	6-Feb-07	.	12-Feb-07	.
1	22-Feb-07	.	.	12-Feb-07	.
1	5-Mar-07	.	.	12-Feb-07	.
1	26-Mar-07	.	26-Mar-07	12-Feb-07	26-Mar-07

How can I achieve this with sas coding?
Thank you in advance.

Re: creating episodes using multiple dates when not clean sorted

SASJedi — Fri, 21 Mar 2025 12:31:05 GMT

I don't have the bandwidth to work on this right now, but here's code to create the HAVE dataset for testing:

data have;
  infile datalines dsd truncover;
  input ID:32. trgt_dt:32. A1600:32. A1700:32. A1800:32. R4:32. A2100:32.;
  format trgt_dt A1600 R4 DATE11.;
datalines4;
1,14770,,,,,
1,14775,14763,1,3,,
1,14783,,,,14783,1
1,16022,,,,,
1,16028,16015,1,3,,
1,16031,,,,16031,1
1,17189,17182,1,3,,
1,17195,,,,,
1,17197,,,,17197,1
1,17198,,,,,
1,17208,17197,1,3,17208,3
1,17209,17209,2,3,,
1,17216,17203,1,3,,
1,17219,,,,,
1,17230,,,,,
1,17251,,,,17251,1
;;;;

Re: creating episodes using multiple dates when not clean sorted

indrakshiroy90 — Fri, 21 Mar 2025 13:34:20 GMT

Thank you @SASJedi. If you are able to give some suggestions later when you get a chance, I would greatly appreciate it.

Re: creating episodes using multiple dates when not clean sorted

mkeintz — Sat, 22 Mar 2025 01:07:19 GMT

Please state a clear and complete rule for assigning stay_start and stay_end. I cannot conjure up a rule to satisfy all the results you show.