Find days difference between diagnosis to treatment, treatment to treatment and overlap

[chirag1401]

Hi all,

As an intermediate user of SAS 9.4, I have tried many different ways to find solution.  Below is sample data which is defined as below. So this is basically patient's journey to clinic starting with a particular diagnosis date and ends with treatment. Trying to find days between initial diagnosis to either tx1 or tx2 or both. also days between each diagnosis and each treatment to have sum of entire treatment from that initial diagnosis. Tried many different ways including SQL min-max, data step first. last., even tried Difference and LAG, lead dates but difference is not calculated correctly. This is a sample dataset. Greatly appreciate your suggestions.  Thanks.

Sum days= Last tx day - initial dx day

dx to dx= last dx date- prev. dx date

tx1 to dx= tx1 date-prev. dx date

tx2 to dx= tx2 date-prev. dx date

tx to tx= tx date-prev. tx date

txb4dx= dx date-tx date (example ID 5)

variables such as dx1_ct, tx1_ct, tx2_ct are total distinct dates for individual variable

I would also like to know if there is overlap between two treatments (yes/no)

	Have
ID	Dx1date	TX1date	TX2date	DX1_ct	TX1_ct	TX2_ct
1			8/7/2018	2	0	1
1	8/6/2018			2	0	1
1	8/7/2018			2	0	1
2				2	0	0
2	2/16/2018			2	0	0
2	3/1/2018			2	0	0
3				7	3	0
3	4/2/2018			7	3	0
3	4/2/2018	4/2/2018		7	3	0
3	4/16/2018			7	3	0
3	4/16/2018	4/16/2018		7	3	0
3	4/19/2018			7	3	0
3	4/30/2018			7	3	0
3	4/30/2018	4/30/2018		7	3	0
3	5/30/2018			7	3	0
3	10/1/2018			7	3	0
3	10/31/2018			7	3	0
4				2	2	5
4			7/31/2018	2	2	5
4			8/26/2018	2	2	5
4			9/28/2018	2	2	5
4			11/1/2018	2	2	5
4			12/8/2018	2	2	5
4	4/20/2018			2	2	5
4	4/20/2018	4/20/2018		2	2	5
4	11/1/2018			2	2	5
4	11/1/2018	11/1/2018		2	2	5
5				1	10	1
5			12/8/2018	1	10	1
5		1/15/2018		1	10	1
5		2/14/2018		1	10	1
5		2/16/2018		1	10	1
5		3/14/2018		1	10	1
5		3/19/2018		1	10	1
5		3/21/2018		1	10	1
5		4/20/2018		1	10	1
5		4/23/2018		1	10	1
5	2/20/2018	5/21/2018		1	10	1
5		5/23/2018		1	10	1

	want
ID	Dx1date	TX1date	TX2date	DX1_ct	TX1_ct	TX2_ct	txB4dx	dx1todx1	tx1todx	tx2todx	tx to tx	Sum Days
1			8/7/2018	2	0	1
1	8/6/2018			2	0	1
1	8/7/2018			2	0	1		1		1		1
2				2	0	0
2	2/16/2018			2	0	0
2	3/1/2018			2	0	0		14				14
3				7	3	0
3	4/2/2018			7	3	0
3	4/2/2018	4/2/2018		7	3	0			0
3	4/16/2018			7	3	0
3	4/16/2018	4/16/2018		7	3	0		15	0
3	4/19/2018			7	3	0		4
3	4/30/2018			7	3	0
3	4/30/2018	4/30/2018		7	3	0		12	0			45
3	5/30/2018			7	3	0		31
3	10/1/2018			7	3	0		125
3	10/31/2018			7	3	0		31
4				2	2	5
4			7/31/2018	2	2	5				103
4			8/26/2018	2	2	5					27
4			9/28/2018	2	2	5					34
4			11/1/2018	2	2	5					35
4			12/8/2018	2	2	5					38	233
4	4/20/2018			2	2	5
4	4/20/2018	4/20/2018		2	2	5		0	0
4	11/1/2018			2	2	5
4	11/1/2018	11/1/2018		2	2	5		0	0
5				1	10	1
5			12/8/2018	1	10	1
5		1/15/2018		1	10	1	37
5		2/14/2018		1	10	1					31
5		2/16/2018		1	10	1					3
5		3/14/2018		1	10	1			23		27
5		3/19/2018		1	10	1					6
5		3/21/2018		1	10	1					3
5		4/20/2018		1	10	1					31
5		4/23/2018		1	10	1					4
5	2/20/2018	5/21/2018		1	10	1					29
5		5/23/2018		1	10	1					3	93

[HarrySnart]

Hi @chirag1401 , Have you tried the INTCK function? https://blogs.sas.com/content/iml/2017/05/15/intck-intnx-intervals-sas.html. You can set the time difference in days between the two dates. 

[chirag1401]

Yes, I have tried INTCK function as well without any success.  

[andreas_lds]

Providing the data you have in usable form (a data step using datalines/cards) makes it easier for us to provide help.

And please explain which tx day should be used if both are not missing.

Sum days= Last tx day - initial dx day

[chirag1401]

Apologies.  TX1 should be used as treatment day if both are not missing.

[chirag1401]

Here is the data.

data Have
input ID $ Dx1date TX1date TX2date DX1_ct TX1_ct TX2_ct;
datalines;

1 8/7/2018 2 0 1
1 8/6/2018 2 0 1
1 8/7/2018 2 0 1
2 2 0 0
2 2/16/2018 2 0 0
2 3/1/2018 2 0 0
3 7 3 0
3 4/2/2018 7 3 0
3 4/2/2018 4/2/2018 7 3 0
3 4/16/2018 7 3 0
3 4/16/2018 4/16/2018 7 3 0
3 4/19/2018 7 3 0
3 4/30/2018 7 3 0
3 4/30/2018 4/30/2018 7 3 0
3 5/30/2018 7 3 0
3 10/1/2018 7 3 0
3 10/31/2018 7 3 0
4 2 2 5
4 7/31/2018 2 2 5
4 8/26/2018 2 2 5
4 9/28/2018 2 2 5
4 11/1/2018 2 2 5
4 12/8/2018 2 2 5
4 4/20/2018 2 2 5
4 4/20/2018 4/20/2018 2 2 5
4 11/1/2018 2 2 5
4 11/1/2018 11/1/2018 2 2 5
5 1 10 1
5 12/8/2018 1 10 1
5 1/15/2018 1 10 1
5 2/14/2018 1 10 1
5 2/16/2018 1 10 1
5 3/14/2018 1 10 1
5 3/19/2018 1 10 1
5 3/21/2018 1 10 1
5 4/20/2018 1 10 1
5 4/23/2018 1 10 1
5 2/20/2018 5/21/2018 1 10 1
5 5/23/2018 1 10 1
;
run;