topic Re: Assigning episode ID in vertical data by interval between records in SAS Programming

Assigning episode ID in vertical data by interval between records

Solph — Mon, 14 Aug 2017 19:34:44 GMT

I've a vertical dataset recording people's history of coming on and off a program. I wanted to assign episode IDs, allowing same episode ID if continuing the program.

- Once joinging the program, the person will be reviewed every now and then, and if approval upon reviewing, their membership gets renewed - when it happens, start date of the program is 0 or 1 day after end date of the previous record.

- If the person returns to the program after an extended period of time (>=2 days), its a different episode ID.

The data has only person ID but no episode ID. So I need to assign them so as to grab their program start date and end date based on episode ID. (I put the wanted values in the last columns from the right). I'm not good at doing vertical data and use retain, etc. Hope you can help out. Once I've the episode IDs, I know how to the rest.

I've created enddt_prev to retain date from the previous record and calculated day interval between records. I added description for seleted cases, if it helps.

data have; input 
ID	1-3 Prog_StartDt $ 5-14 Prog_EndDt $ 16-25 Enddt_prev $ 27-36 interval 38-40 EpisodeID 42 Epi_startdt $ 44-53 
Epi_enddt $ 55-64;
datalines;
101 01/09/2013 04/08/2013                1 01/09/2013 04/08/2013
105 12/16/2014 03/15/2015 04/08/2013     1 12/16/2014 11/30/2015  105: 1 epi - start date
105	03/16/2015 11/30/2015 03/15/2015   1 1                        105: 1 epi - end date
107	04/20/2015 07/18/2015                1 04/20/2015 07/18/2015  107: 1st epi
107	05/16/2016 08/12/2016 07/18/2015 303 2 05/16/2016 08/12/2016  107: 2nd epi
204	08/13/2015 11/10/2015                1 08/13/2015 11/10/2015  204: 1st epi (next is 92 days later)
204	02/10/2016 05/09/2016 11/10/2015  92 2 02/10/2016 01/31/2017  204: 2nd epi - start date
204	05/10/2016 01/31/2017 05/09/2016   1 2                        204: 2nd epi - end date
215	05/31/2015 07/20/2015                1 05/31/2015 07/20/2015  205 1st epi (next is 260 days later)
215	04/05/2016 07/03/2016 07/20/2015 260 2 04/05/2016 03/31/2017  215: 2nd epi - start date
215	07/04/2016 03/31/2017 07/03/2016   1 2                        215: 2nd epi - end date
303	06/04/2012 02/28/2013                1 06/04/2012 02/28/2013
303	10/16/2015 01/14/2016 02/28/2013 960 2 10/16/2015 01/29/2017
303	01/15/2016 09/30/2016 01/14/2016   1 2
303	10/01/2016 01/29/2017 09/30/2016   1 2
401	03/06/2012 11/30/2012                1 03/06/201211/30/2012
401	11/10/2013 02/09/2014 11/30/2012 345 2 11/10/201310/31/2014
401	02/10/2014 10/31/2014 02/09/2014   1 2
401	02/01/2015 06/01/2015 10/31/2014  93 3 02/01/2015 06/01/2015
505	01/06/2012 11/09/2012                1 01/06/2012 02/09/2014
505	11/10/2012 02/09/2014 11/09/2012   1 1
505	04/10/2014 10/31/2014 02/09/2014  60 2 04/10/2014 03/01/2015
505	11/01/2014 03/01/2015 10/31/2014   1 2
;
proc print; run;

Re: Assigning episode ID in vertical data by interval between records

Reeza — Mon, 14 Aug 2017 19:26:50 GMT

Use the code editor when inserting code, then it can maintain the spaces. As you've noted your code to read the data doesn't work properly.

Re: Assigning episode ID in vertical data by interval between records

Solph — Mon, 14 Aug 2017 19:35:44 GMT

Thanks for the tips. The code is edited in the orignial post.

Re: Assigning episode ID in vertical data by interval between records

Reeza — Mon, 14 Aug 2017 19:45:41 GMT

This is the same as the 30/60/90 day readmission problem. There are solutions on here that will work for your data if you search with those terms.

I think it's just the following but don't have time to test anything now. This can get you started and if you have issues post your questions with your new code.

data want;
set have;
by patient_id;

retain episode_ID;

if first.patient_ID then episode_id =1;
else if day_interval > 1 then episode_id + 1;

run;

Re: Assigning episode ID in vertical data by interval between records

Solph — Mon, 14 Aug 2017 19:53:58 GMT

Indeed it worked perfectively. Thanks so much. I'll file it away.

data want;
set have (drop=Epi_startdt Epi_enddt rename=(id=patient_id interval=day_interval episodeID=EID_want));
by patient_id;

retain episode_ID;

if first.patient_ID then episode_id =1;
else if day_interval > 1 then episode_id + 1;

run;
proc print noobs; run;

Re: Assigning episode ID in vertical data by interval between records

SAS_inquisitive — Tue, 15 Aug 2017 00:32:25 GMT

@Reeza I have a question regarding your solution. Does ELSE IF statment also imply not first.patient_ID here?

Thanks !

Re: Assigning episode ID in vertical data by interval between records

Reeza — Tue, 15 Aug 2017 02:02:01 GMT

@SAS_inquisitive wrote:

@Reeza I have a question regarding your solution. Does ELSE IF statment also imply not first.patient_ID here?

Thanks !

Anything not meeting the first condition is tested in the second condition.