topic Re: Date Calculation for death in SAS Programming

Date Calculation for death

newsas007 — Mon, 24 Aug 2020 06:26:12 GMT

Hi all,

I am working with a large set of dates and would like to get some help calculating at what point death occurs. The dataset has start and end dates for different lines of therapy and a death date associated with it for people who expired. I am trying to calculate at what point the death occurs. The rule is that it should be captured within the start and end of lines or 180 days after the line has ended. For example #ID 1 death occurs one year after end of line of therapy 3, so therefore coded as 0 in all lines. Also, once they are deceased, they are left blank in the subsequent lines.

Here is the data set:

ID	Death_Date	Start_line1	End_line1	Start_line2	End_line2	Start_line3	End_line3	deceased
1	2/20/2018	12/31/2015	5/1/2016	5/5/2016	9/15/2016	9/22/2016	12/14/2016	1
2		7/28/2016	3/5/2017	3/6/2017	8/17/2017			0
3	4/21/2017	7/4/2016	10/18/2016	10/19/2016	12/11/2016			1
4	2/17/2017					2/18/2016	8/16/2016	1
5	11/2/2016			6/5/2016	8/16/2016	8/17/2016	11/2/2016	1
6	1/12/2018	11/26/2015	3/2/2016	2/1/2017	8/8/2017			1
7	3/29/2020	12/2/2015	6/29/2016	6/30/2016	1/27/2017	5/8/2018	2/8/2019	1
8	10/7/2016	5/3/2015	7/4/2015	7/5/2015	1/26/2016	9/4/2016	10/7/2016	1
9	5/20/2017	11/11/2015	7/2/2016	7/3/2016	9/26/2016	9/27/2016	10/23/2016	1
11	6/6/2017	4/23/2014	10/14/2014	5/25/2015	6/15/2015	8/24/2015	4/17/2016	1

Here is the dataset i would like to have:

ID	Death_Date	Start_line1	End_line1	Start_line2	End_line2	Start_line3	End_line3	deceased	Deceased_line1	Deceased_line2	Deceased_line3
1	2/20/2018	12/31/2015	5/1/2016	5/5/2016	9/15/2016	9/22/2016	12/14/2016	1	0	0	0
2		7/28/2016	3/5/2017	3/6/2017	8/17/2017			0	0	0	0
3	4/21/2017	7/4/2016	10/18/2016	10/19/2016	12/11/2016			1	0	1
4	2/17/2017					2/18/2016	8/16/2016	1	0	0	0
5	11/2/2016			6/5/2016	8/16/2016	8/17/2016	11/2/2016	1	0	0	1
6	1/12/2018	11/26/2015	3/2/2016	2/1/2017	8/8/2017			1	0	1
7	3/29/2020	12/2/2015	6/29/2016	6/30/2016	1/27/2017	5/8/2018	2/8/2019	1	0	0	0
8	10/7/2016	5/3/2015	7/4/2015	7/5/2015	1/26/2016	9/4/2016	10/7/2016	1	0	0	1
9	5/20/2017	11/11/2015	7/2/2016	7/3/2016	9/26/2016	9/27/2016	10/23/2016	1	0	0	0
11	6/6/2017	4/23/2014	10/14/2014	5/25/2015	6/15/2015	8/24/2015	4/17/2016	1	0	0	0

Re: Date Calculation for death

PeterClemmensen — Mon, 24 Aug 2020 06:43:01 GMT

Do you have three lines in your actual data as well?

Re: Date Calculation for death

newsas007 — Mon, 24 Aug 2020 07:07:39 GMT

Yes, there are 3 lines. Thanks

Re: Date Calculation for death

PeterClemmensen — Mon, 24 Aug 2020 07:23:02 GMT

Shouldn't Deceased_line2=1 for ID=5?

Re: Date Calculation for death

newsas007 — Mon, 24 Aug 2020 07:30:55 GMT

ID#5 death date is 11/2/2016. that is the end of the line of therapy 3. Line 3 ended on death date

Re: Date Calculation for death

PeterClemmensen — Mon, 24 Aug 2020 07:34:02 GMT

True. But 11/2/2016 is within 180 days of 08/16/2016 (End_Line2) ?

Re: Date Calculation for death

PeterClemmensen — Mon, 24 Aug 2020 07:38:29 GMT

I think this is what you want

data have;
infile datalines dlm=',';
input ID (Death_Date Start_line1 End_line1 Start_line2 End_line2 Start_line3 End_line3)(:mmddyy10.) deceased;
format Death_Date Start_line1 End_line1 Start_line2 End_line2 Start_line3 End_line3 mmddyy10.;
datalines;
1 ,2/20/2018,12/31/2015,5/1/2016  ,5/5/2016  ,9/15/2016 ,9/22/2016,12/14/2016,1
2 ,         ,7/28/2016 ,3/5/2017  ,3/6/2017  ,8/17/2017 ,         ,          ,0
3 ,4/21/2017,7/4/2016  ,10/18/2016,10/19/2016,12/11/2016,         ,          ,1
4 ,2/17/2017,          ,          ,          ,          ,2/18/2016,8/16/2016 ,1
5 ,11/2/2016,          ,          ,6/5/2016  ,8/16/2016 ,8/17/2016,11/2/2016 ,1
6 ,1/12/2018,11/26/2015,3/2/2016  ,2/1/2017  ,8/8/2017  ,         ,          ,1
7 ,3/29/2020,12/2/2015 ,6/29/2016 ,6/30/2016 ,1/27/2017 ,5/8/2018 ,2/8/2019  ,1
8 ,10/7/2016,5/3/2015  ,7/4/2015  ,7/5/2015  ,1/26/2016 ,9/4/2016 ,10/7/2016 ,1
9 ,5/20/2017,11/11/2015,7/2/2016  ,7/3/2016  ,9/26/2016 ,9/27/2016,10/23/2016,1
11,6/6/2017 ,4/23/2014 ,10/14/2014,5/25/2015 ,6/15/2015 ,8/24/2015,4/17/2016 ,1
;

data want;
  set have;
  Deceased_line1 = (Start_line1 <= Death_Date <= End_line1 + 180 & deceased);
  Deceased_line2 = ifn(Deceased_line1, ., (Start_line2 <= Death_Date <= End_line2 + 180 & deceased));
  Deceased_line3 = ifn(Deceased_line2, ., (Start_line3 <= Death_Date <= End_line3 + 180 & deceased));
run;

Result (posted without line dates for simplicity)

ID Death_Date   deceased Deceased_line1 Deceased_line2 Deceased_line3 
1  02/20/2018   1        0              0              0 
2  .            0        0              0              0 
3  04/21/2017   1        0              1              . 
4  02/17/2017   1        0              0              0 
5  11/02/2016   1        0              1              . 
6  01/12/2018   1        0              1              . 
7  03/29/2020   1        0              0              0 
8  10/07/2016   1        0              0              1 
9  05/20/2017   1        0              0              0 
11 06/06/2017   1        0              0              0

Re: Date Calculation for death

newsas007 — Mon, 24 Aug 2020 08:08:24 GMT

Good point. 180 days if it's the last line and there is no preceding line.

Re: Date Calculation for death

newsas007 — Mon, 24 Aug 2020 14:24:35 GMT

This is good, but #5 deceased_line3 = 1 because the death happened in third line. They also got the third line treatment.
The 180 day rule should be only if there are no more lines.

Re: Date Calculation for death

newsas007 — Mon, 24 Aug 2020 17:13:03 GMT

I tried to change some options but it is not working. If there is no subsequent line starting then the deceased=1 if the death happens within first 180 days or deceased=0 otherwise..