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;
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;
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.
Thanks for the tips. The code is edited in the orignial post.
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;
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;
@Reeza I have a question regarding your solution. Does ELSE IF statment also imply not first.patient_ID here?
Thanks !
@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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.