Date Calculation for death

[newsas007]

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

[PeterClemmensen]

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

[newsas007]

Yes, there are 3 lines. Thanks

[PeterClemmensen]

Shouldn't Deceased_line2=1 for ID=5?

[newsas007]

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

[PeterClemmensen]

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

[newsas007]

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

[PeterClemmensen]

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 

[newsas007]

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.

[newsas007]

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..