topic Re: Difficult MERGE problem that Technical Support couldn't answer in SAS Programming

Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Thu, 24 Sep 2020 16:24:02 GMT

This is a particularly hard nut for me to crack. In fact, SAS Technical Support wasn't able to help. CAN YOU? . The data comes from a hospital and its one intensive care unit (ICU). Please see the table below that has a sample of the data (anonymized of course).

MAIN				VITALS			FINAL
ID	hadmit	hdischarge	mortality	ID	istart	istop	ID	hnum	visit	mortality	hadmit	istart	istop	hdischarge
51001	9/4/20 10:00 AM	9/5/20 11:12 AM	0	51001	9/4/20 10:00 AM	9/4/20 11:12 PM	51001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/4/20 11:12 PM	9/5/20 11:12 AM
51002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	51002	9/4/20 12:52 PM	9/6/20 10:28 PM	51002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/6/20 10:28 PM	9/10/20 12:52 PM
51002	9/4/20 10:57 AM	9/10/20 12:52 PM	1	51002	9/8/20 3:16 AM	9/10/20 5:40 AM	51002	1	2	0	9/4/20 10:57 AM	9/8/20 3:16 AM	9/10/20 5:40 AM	9/10/20 12:52 PM
51003	9/6/20 5:12 AM	9/10/20 2:20 AM	0	51003	9/6/20 5:13 AM	9/8/20 11:56 AM	51003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
51003	9/11/20 4:44 PM	9/12/20 10:01 AM	1	51003	9/11/20 5:56 PM	9/12/20 10:01 AM	51003	2	1	1	9/11/20 4:44 PM	9/11/20 5:56 PM	9/12/20 10:01 AM	9/12/20 10:01 AM
51004	9/15/20 9:32 PM	9/18/20 9:39 PM	0	51004	9/16/20 9:32 AM	9/17/20 9:32 PM	51004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
51004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	51004	9/23/20 2:20 AM	9/24/20 3:32 AM	51004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
51004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	51004	9/26/20 5:56 PM	9/28/20 10:30 AM	51004	2	2	0	9/22/20 2:20 AM	9/26/20 5:56 PM	9/28/20 10:30 AM	9/29/20 5:13 AM
51005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	51005	9/22/20 11:56 AM	9/25/20 11:56 AM	51005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
51005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	51005	9/26/20 12:25 AM	9/26/20 4:44 PM	51005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
51005	##############	10/8/20 3:18 PM	0	51005	10/2/20 1:08 PM	10/5/20 6:11 AM	51005	2	1	0	##############	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
51005	##############	10/8/20 7:51 AM	1	51005	10/6/20 8:06 AM	10/8/20 7:51 AM	51005	2	2	1	##############	10/6/20 8:06 AM	10/8/20 7:51 AM	10/8/20 7:51 AM
61001	9/4/20 10:00 AM		0	61001	9/4/20 10:00 AM	9/4/20 11:12 PM	61003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
61002	9/4/20 10:57 AM		0	61002	9/4/20 12:52 PM	9/6/20 10:28 PM	61004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
61002	9/4/20 10:57 AM		0	61002	9/8/20 3:16 AM	9/10/20 5:40 AM	61005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
61003	9/6/20 5:12 AM	9/10/20 2:20 AM	0	61003	9/6/20 5:13 AM	9/8/20 11:56 AM	61005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
61003	9/11/20 4:44 PM		1	61003	9/11/20 5:56 PM	9/12/20 10:01 AM	71001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/5/20 7:07 AM	9/5/20 11:12 AM
61004	9/15/20 9:32 PM	9/18/20 9:39 PM	0	61004	9/16/20 9:32 AM	9/17/20 9:32 PM	71002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/6/20 10:28 PM	9/10/20 12:52 PM
61004	9/22/20 2:20 AM		0	61004	9/23/20 2:20 AM	9/24/20 3:32 AM	71003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
61004	9/22/20 2:20 AM		0	61004	9/26/20 5:56 PM	9/28/20 10:30 AM	71004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
61005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	61005	9/22/20 11:56 AM	9/25/20 11:56 AM	71004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
61005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	61005	9/26/20 12:25 AM	9/26/20 4:44 PM	71005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
61005	##############		0	61005	10/2/20 1:08 PM	10/5/20 6:11 AM	71005	2	1	0	##############	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
61005	##############		1	61005	10/6/20 8:06 AM	10/8/20 7:51 AM	71005	2	2	0	##############	10/6/20 8:06 AM	10/8/20 7:51 AM	10/8/20 2:01 PM
71001	9/4/20 10:00 AM	9/5/20 11:12 AM	0	71001	9/4/20 10:00 AM	9/5/20 7:07 AM	81001	1	1	1	9/4/20 10:00 AM	9/4/20 10:00 AM	9/5/20 11:12 AM	9/5/20 11:12 AM
71002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	71002	9/4/20 12:52 PM	9/6/20 10:28 PM	81002	1	1	0	9/4/20 10:57 AM	9/8/20 3:16 AM	9/10/20 5:40 AM	9/10/20 12:52 PM
71002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	71002	9/8/20 3:16 AM	9/8/20 5:12 AM	81003	1	1	0	9/9/20 11:19 AM	9/9/20 12:31 PM	9/10/20 4:36 AM	9/10/20 4:36 AM
71003	9/6/20 5:12 AM	9/10/20 2:20 AM	0	71003	9/6/20 5:13 AM	9/8/20 11:56 AM	81004	1	1	0	9/19/20 8:55 PM	9/23/20 11:40 AM	9/25/20 4:14 AM	9/25/20 10:57 PM
71003	9/11/20 4:44 PM	9/11/20 5:59 PM	1	71003	9/11/20 5:56 PM	9/11/20 5:59 PM	81005	1	1	0	9/20/20 1:43 AM	9/20/20 8:01 PM	9/24/20 11:19 AM	9/24/20 4:07 PM
71004	9/15/20 9:32 PM	9/18/20 9:39 PM	0	71004	9/16/20 9:32 AM	9/17/20 9:32 PM	81005	2	1	1	9/30/20 7:28 AM	10/1/20 11:22 AM	10/6/20 9:52 AM	10/6/20 9:52 AM
71004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	71004	9/23/20 2:20 AM	9/24/20 3:32 AM	91001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/4/20 12:53 PM	9/5/20 11:12 AM
71004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	71004	9/26/20 5:56 PM	9/26/20 5:58 PM	91002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/6/20 10:28 PM	9/10/20 12:52 PM
71005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	71005	9/22/20 11:56 AM	9/25/20 11:56 AM	91002	2	2	0	9/4/20 10:57 AM	9/7/20 3:16 AM	9/10/20 5:40 AM	9/10/20 12:52 PM
71005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	71005	9/26/20 12:25 AM	9/26/20 12:34 AM	91003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
71005	##############	10/8/20 3:18 PM	0	71005	10/2/20 1:08 PM	10/5/20 6:11 AM	91003	2	1	1	9/11/20 4:44 PM	9/11/20 5:56 PM	9/12/20 10:01 AM	9/12/20 10:01 AM
71005	##############	10/8/20 3:18 PM	1	71005	10/6/20 8:06 AM	10/8/20 7:51 AM	91004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
81001	9/4/20 10:00 AM	9/5/20 11:12 AM	1	81001	9/4/20 10:00 AM	9/5/20 11:12 AM	91004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
81002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	81002	9/4/20 12:52 PM	9/4/20 2:22 PM	91004	2	2	0	9/22/20 2:20 AM	9/24/20 8:49 AM	9/28/20 10:30 AM	9/29/20 5:13 AM
81002	9/4/20 10:57 AM	9/10/20 12:52 PM	1	81002	9/8/20 3:16 AM	9/10/20 5:40 AM	91005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
81003	9/6/20 5:12 AM	9/7/20 8:55 PM	0	81003	9/6/20 5:13 AM	9/6/20 6:31 AM	91005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
81003	9/9/20 11:19 AM	9/10/20 4:36 AM	0	81003	9/9/20 12:31 PM	9/10/20 4:36 AM	91005	2	1	0	##############	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
81004	9/13/20 4:07 PM	9/15/20 6:03 AM	0	81004	9/14/20 4:07 AM	9/14/20 5:56 AM	91005	2	2	0	##############	10/5/20 9:47 AM	10/8/20 7:51 AM	10/8/20 3:18 PM
81004	9/19/20 8:55 PM	9/25/20 10:57 PM	0	81004	9/20/20 8:55 PM	9/20/20 9:16 PM	101003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
81004	9/19/20 8:55 PM	9/25/20 10:57 PM	1	81004	9/23/20 11:40 AM	9/25/20 4:14 AM	101004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
81005	9/20/20 1:43 AM	9/24/20 4:07 PM	0	81005	9/20/20 6:31 AM	9/20/20 7:33 AM	101005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
81005	9/20/20 1:43 AM	9/24/20 4:07 PM	0	81005	9/20/20 8:01 PM	9/24/20 11:19 AM	101005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
81005	9/30/20 7:28 AM	10/6/20 9:52 AM	0	81005	9/30/20 7:43 AM	9/30/20 9:27 AM	111001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/5/20 7:07 AM	9/5/20 11:12 AM
81005	9/30/20 7:28 AM	10/6/20 9:52 AM	1	81005	10/1/20 11:22 AM	10/6/20 9:52 AM	111002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/8/20 2:19 AM	9/10/20 12:52 PM
91001	9/4/20 10:00 AM	9/5/20 11:12 AM	0	91001	9/4/20 10:00 AM	9/4/20 12:53 PM	111003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
91002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	91002	9/4/20 12:52 PM	9/6/20 10:28 PM	111003	2	1	1	9/8/2020 3:00PM	9/8/20 4:44 PM	9/11/20 5:59 PM	9/11/20 5:59 PM
91002	9/4/20 10:57 AM	9/10/20 12:52 PM	1	91002	9/7/20 3:16 AM	9/10/20 5:40 AM	111004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
91003	9/6/20 5:12 AM	9/10/20 2:20 AM	0	91003	9/6/20 5:13 AM	9/8/20 11:56 AM	111004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
91003	9/11/20 4:44 PM	9/12/20 10:01 AM	1	91003	9/11/20 5:56 PM	9/12/20 10:01 AM	111005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
91004	9/15/20 9:32 PM	9/18/20 9:39 PM	0	91004	9/16/20 9:32 AM	9/17/20 9:32 PM	111005	2	1	0	##############	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
91004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	91004	9/23/20 2:20 AM	9/24/20 3:32 AM	111005	2	2	0	##############	10/5/20 10:59 AM	10/8/20 7:51 AM	10/8/20 3:18 PM
91004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	91004	9/24/20 8:49 AM	9/28/20 10:30 AM	121002	1	1	0	9/4/20 10:57 AM	9/4/20 1:50 PM	9/10/20 5:40 AM	9/10/20 12:52 PM
91005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	91005	9/22/20 11:56 AM	9/25/20 11:56 AM	121003	1	1	0	9/9/20 11:19 AM	9/9/20 12:31 PM	9/10/20 4:36 AM	9/10/20 4:36 AM
91005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	91005	9/26/20 12:25 AM	9/26/20 4:44 PM	121004	1	1	0	9/19/20 8:55 PM	9/19/20 11:19 PM	9/20/20 9:16 PM	9/25/20 10:57 PM
91005	##############	10/8/20 3:18 PM	0	91005	10/2/20 1:08 PM	10/5/20 6:11 AM	121004	1	2	1	9/19/20 8:55 PM	9/21/20 2:04 AM	9/25/20 10:57 PM	9/25/20 10:57 PM
91005	##############	10/8/20 3:18 PM	1	91005	10/5/20 9:47 AM	10/8/20 7:51 AM	121005	1	1	0	9/20/20 1:43 AM	9/20/20 10:11 AM	9/24/20 11:19 AM	9/24/20 4:07 PM
101001	9/4/20 10:00 AM		0	101001	9/4/20 10:00 AM	9/4/20 11:12 PM	121005	2	1	0	9/30/20 7:28 AM	10/1/20 11:22 AM	10/3/20 11:08 AM	10/6/20 9:52 AM
101002	9/4/20 10:57 AM		0	101002	9/4/20 12:52 PM	9/6/20 10:28 PM
101002	9/4/20 10:57 AM		0	101002	9/8/20 3:16 AM	9/10/20 5:40 AM
101003	9/6/20 5:12 AM	9/10/20 2:20 AM	0	101003	9/6/20 5:13 AM	9/8/20 11:56 AM
101003	9/11/20 4:44 PM		1	101003	9/11/20 5:56 PM	9/12/20 10:01 AM
101004	9/15/20 9:32 PM	9/18/20 9:39 PM	0	101004	9/16/20 9:32 AM	9/17/20 9:32 PM
101004	9/22/20 2:20 AM		0	101004	9/23/20 2:20 AM	9/24/20 3:32 AM
101004	9/22/20 2:20 AM		0	101004	9/24/20 8:49 AM	9/28/20 10:30 AM
101005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	101005	9/22/20 11:56 AM	9/25/20 11:56 AM
101005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	101005	9/26/20 12:25 AM	9/26/20 4:44 PM
101005	##############		0	101005	10/2/20 1:08 PM	10/5/20 6:11 AM
101005	##############		1	101005	10/5/20 10:01 AM	10/8/20 7:51 AM
111001	9/4/20 10:00 AM	9/5/20 11:12 AM	0	111001	9/4/20 10:00 AM	9/5/20 7:07 AM
111002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	111002	9/4/20 12:52 PM	9/8/20 2:19 AM
111002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	111002	9/8/20 3:16 AM	9/8/20 5:12 AM
111003	9/6/20 5:12 AM	9/10/20 2:20 AM	0	111003	9/6/20 5:13 AM	9/8/20 11:56 AM
111003	9/8/2020 3:00PM	9/11/20 5:59 PM	1	111003	9/8/20 4:44 PM	9/11/20 5:59 PM
111004	9/15/20 9:32 PM	9/18/20 9:39 PM	0	111004	9/16/20 9:32 AM	9/17/20 9:32 PM
111004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	111004	9/23/20 2:20 AM	9/24/20 3:32 AM
111004	9/22/20 2:20 AM	9/29/20 5:13 AM	0	111004	9/26/20 5:56 PM	9/26/20 5:58 PM
111005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	111005	9/22/20 11:56 AM	9/25/20 11:56 AM
111005	9/22/20 7:08 AM	9/26/20 9:32 PM	0	111005	9/26/20 12:25 AM	9/26/20 12:34 AM
111005	##############	10/8/20 3:18 PM	0	111005	10/2/20 1:08 PM	10/5/20 6:11 AM
111005	##############	10/8/20 3:18 PM	1	111005	10/5/20 10:59 AM	10/8/20 7:51 AM
121001	9/4/20 10:00 AM	9/5/20 11:12 AM	1	121001	9/4/20 10:00 AM	9/4/20 10:43 PM
121002	9/4/20 10:57 AM	9/10/20 12:52 PM	0	121002	9/4/20 12:52 PM	9/4/20 2:22 PM
121002	9/4/20 10:57 AM	9/10/20 12:52 PM	1	121002	9/4/20 5:14 PM	9/10/20 5:40 AM
121003	9/6/20 5:12 AM	9/7/20 8:55 PM	0	121003	9/6/20 5:13 AM	9/6/20 6:31 AM
121003	9/9/20 11:19 AM	9/10/20 4:36 AM	0	121003	9/9/20 12:31 PM	9/10/20 4:36 AM
121004	9/13/20 4:07 PM	9/15/20 6:03 AM	0	121004	9/14/20 4:07 AM	9/14/20 5:56 AM
121004	9/19/20 8:55 PM	9/25/20 10:57 PM	0	121004	9/19/20 11:19 PM	9/20/20 9:16 PM
121004	9/19/20 8:55 PM	9/25/20 10:57 PM	1	121004	9/21/20 2:04 AM	9/25/20 10:57 PM
121005	9/20/20 1:43 AM	9/24/20 4:07 PM	0	121005	9/20/20 6:31 AM	9/20/20 7:33 AM
121005	9/20/20 1:43 AM	9/24/20 4:07 PM	0	121005	9/20/20 10:11 AM	9/24/20 11:19 AM
121005	9/30/20 7:28 AM	10/6/20 9:52 AM	0	121005	9/30/20 7:43 AM	9/30/20 9:27 AM
121005	9/30/20 7:28 AM	10/6/20 9:52 AM	1	121005	10/1/20 11:22 AM	10/3/20 11:08 AM

There are two data sets that I want to combine into one data set, but there are no strong primary keys. One data set is called “MAIN”, and it has each hospitalization listed once, with admit and discharge date-times, and whether or not the patient died before discharge. The other is called “VITALS”, and it has the start and stop date-times when vital signs were taken; these can be e the ICU admit and discharge date-times. I want a data set called “FINAL” in which each observation is an ICU visit, with the visit number, the hospital admit and discharge date-times, ICU admission and discharge date-times, hospitalization (1st, 2nd, etc..) and ICU visit within each hospitalization (1st, 2nd, etc..).

Here are the rules that make things difficult:

A patient may be hospitalized more than once. Within each hospitalization a patient may have one or more visits to the ICU.
Any hospitalization without a discharge date-time (the patient hadn’t been discharged yet) is deleted.
Any ICU visit lasting less than two hours is deleted.
Taking all of the above into account, hospitalizations that aren’t deleted are numbered sequentially for each patient. ICU visits that aren’t deleted are numbered sequentially for each hospitalization.
Whether or not a patient died during an ICU visit is critical. Patients who die will expire during the last ICU visit or after that visit and before hospital discharge. Patients who do not die before hospital discharge have mortality = 0 for all of their ICU sits. That means there are three possibilities: patient dies during last ICU visit and that visit is > 2 hrs. duration gt 2 hrs. duration (mortality = 1); patient dies during last ICU visit but that visit is less than two hours (that ICU visit is visit for that patient gets mortality = 1); or the patient dies after last ICU visit but before hospital discharge (mortality = 0 for each visit). The time of death is assumed to be the hospital discharge date-time, therefore the ICU discharge date-time that matches that is the assumed time of death.
There’s one other problem. Sometimes there’s a delay in recording the hospital discharge date-time, so that a patient’s time of death will be within two hours of hospital discharge. So any death that occurs during an ICU visit that hasn’t been deleted and is within two hours of hospital discharge datetime gets assigned a mortality = 1.

Thank you.

Andrew Kramer

Re: Difficult MERGE problem that Technical Support couldn't answer

PaigeMiller — Thu, 24 Sep 2020 16:46:52 GMT

Hello @DocMartin, this is an interesting problem, and I'd like to take a crack at it, but for me to help, you'd need to provide data in a more usable form (screen captures are not usable). The usable form is a SAS data step, instructions are here: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Re: Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Thu, 24 Sep 2020 17:32:16 GMT

@PaigeMiller

Here is some SAS code that reads in raw data. Some of the formats might not be right; when I'm using this data it's imported from a CSV file.

data main; input ID $	hadmit datetime16.	hdischarge datetime16.	mortality;
cards;
51001	9/4/20 10:00 AM	9/5/20 11:12 AM	0
51002	9/4/20 10:57 AM	9/10/20 12:52 PM 0
51002	9/4/20 10:57 AM	9/10/20 12:52 PM 1
51003	9/6/20 5:12 AM	9/10/20 2:20 AM	0
51003	9/11/20 4:44 PM	9/12/20 10:01 AM 1
51004	9/15/20 9:32 PM	9/18/20 9:39 PM	0
51004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
51004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
51005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
51005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
51005	10/2/20 12:54 PM 10/8/20 3:18 PM 0
51005	10/2/20 12:54 PM 10/8/20 7:51 AM 1
61001	9/4/20 10:00 AM	 .	0
61002	9/4/20 10:57 AM	 .	0
61002	9/4/20 10:57 AM	 .	0
61003	9/6/20 5:12 AM	9/10/20 2:20 AM	0
61003	9/11/20 4:44 PM	 .	1
61004	9/15/20 9:32 PM	9/18/20 9:39 PM	0
61004	9/22/20 2:20 AM	 .	0
61004	9/22/20 2:20 AM	.	0
61005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
61005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
61005	10/2/20 12:54 PM  .	0
61005	10/2/20 12:54 PM	.	1
71001	9/4/20 10:00 AM	9/5/20 11:12 AM	0
71002	9/4/20 10:57 AM	9/10/20 12:52 PM	0
71002	9/4/20 10:57 AM	9/10/20 12:52 PM	0
71003	9/6/20 5:12 AM	9/10/20 2:20 AM	0
71003	9/11/20 4:44 PM	9/11/20 5:59 PM	1
71004	9/15/20 9:32 PM	9/18/20 9:39 PM	0
71004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
71004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
71005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
71005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
71005	10/2/20 12:54 PM 10/8/20 3:18 PM 0
71005	10/2/20 12:54 PM 10/8/20 3:18 PM 1
81001	9/4/20 10:00 AM	9/5/20 11:12 AM	1
81002	9/4/20 10:57 AM	9/10/20 12:52 PM 0
81002	9/4/20 10:57 AM	9/10/20 12:52 PM 1
81003	9/6/20 5:12 AM	9/7/20 8:55 PM	0
81003	9/9/20 11:19 AM	9/10/20 4:36 AM	0
81004	9/13/20 4:07 PM	9/15/20 6:03 AM	0
81004	9/19/20 8:55 PM	9/25/20 10:57 PM 0
81004	9/19/20 8:55 PM	9/25/20 10:57 PM 1
81005	9/20/20 1:43 AM	9/24/20 4:07 PM	0
81005	9/20/20 1:43 AM	9/24/20 4:07 PM	0
81005	9/30/20 7:28 AM	10/6/20 9:52 AM	0
81005	9/30/20 7:28 AM	10/6/20 9:52 AM	1
91001	9/4/20 10:00 AM	9/5/20 11:12 AM	0
91002	9/4/20 10:57 AM	9/10/20 12:52 PM 0
91002	9/4/20 10:57 AM	9/10/20 12:52 PM 1
91003	9/6/20 5:12 AM	9/10/20 2:20 AM	0
91003	9/11/20 4:44 PM	9/12/20 10:01 AM 1
91004	9/15/20 9:32 PM	9/18/20 9:39 PM	0
91004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
91004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
91005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
91005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
91005	10/2/20 12:54 PM 10/8/20 3:18 PM 0
91005	10/2/20 12:54 PM 10/8/20 3:18 PM 1
101001	9/4/20 10:00 AM	 .	0
101002	9/4/20 10:57 AM	 .	0
101002	9/4/20 10:57 AM	 .	0
101003	9/6/20 5:12 AM	9/10/20 2:20 AM	0
101003	9/11/20 4:44 PM	.	1
101004	9/15/20 9:32 PM	9/18/20 9:39 PM	0
101004	9/22/20 2:20 AM	.	0
101004	9/22/20 2:20 AM	.	0
101005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
101005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
101005	10/2/20 12:54 PM	.	0
101005	10/2/20 12:54 PM	.	1
111001	9/4/20 10:00 AM	9/5/20 11:12 AM	0
111002	9/4/20 10:57 AM	9/10/20 12:52 PM 0
111002	9/4/20 10:57 AM	9/10/20 12:52 PM 0
111003	9/6/20 5:12 AM	9/10/20 2:20 AM	0
111003	9/8/2020 3:00PM	9/11/20 5:59 PM	1
111004	9/15/20 9:32 PM	9/18/20 9:39 PM	0
111004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
111004	9/22/20 2:20 AM	9/29/20 5:13 AM	0
111005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
111005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
111005	10/2/20 12:54 PM 10/8/20 3:18 PM  0
111005	10/2/20 12:54 PM 10/8/20 3:18 PM 1
121001	9/4/20 10:00 AM	9/5/20 11:12 AM	1
121002	9/4/20 10:57 AM	9/10/20 12:52 PM 0
121002	9/4/20 10:57 AM	9/10/20 12:52 PM 1
121003	9/6/20 5:12 AM	9/7/20 8:55 PM	0
121003	9/9/20 11:19 AM	9/10/20 4:36 AM	0
121004	9/13/20 4:07 PM	9/15/20 6:03 AM	0
121004	9/19/20 8:55 PM	9/25/20 10:57 PM 0
121004	9/19/20 8:55 PM	9/25/20 10:57 PM  1
121005	9/20/20 1:43 AM	9/24/20 4:07 PM	0
121005	9/20/20 1:43 AM	9/24/20 4:07 PM	0
121005	9/30/20 7:28 AM	10/6/20 9:52 AM	0
121005	9/30/20 7:28 AM	10/6/20 9:52 AM	1
;
run;

data viatls; input ID $	istart datetime16.	istop datetime16.;
cards;
51001	9/4/20 10:00 AM	9/4/20 11:12 PM
51002	9/4/20 12:52 PM	9/6/20 10:28 PM
51002	9/8/20 3:16 AM	9/10/20 5:40 AM
51003	9/6/20 5:13 AM	9/8/20 11:56 AM
51003	9/11/20 5:56 PM	9/12/20 10:01 AM
51004	9/16/20 9:32 AM	9/17/20 9:32 PM
51004	9/23/20 2:20 AM	9/24/20 3:32 AM
51004	9/26/20 5:56 PM	9/28/20 10:30 AM
51005	9/22/20 11:56 AM 9/25/20 11:56 AM
51005	9/26/20 12:25 AM 9/26/20 4:44 PM
51005	10/2/20 1:08 PM	10/5/20 6:11 AM
51005	10/6/20 8:06 AM	10/8/20 7:51 AM
61001	9/4/20 10:00 AM	9/4/20 11:12 PM
61002	9/4/20 12:52 PM	9/6/20 10:28 PM
61002	9/8/20 3:16 AM	9/10/20 5:40 AM
61003	9/6/20 5:13 AM	9/8/20 11:56 AM
61003	9/11/20 5:56 PM	9/12/20 10:01 AM
61004	9/16/20 9:32 AM	9/17/20 9:32 PM
61004	9/23/20 2:20 AM	9/24/20 3:32 AM
61004	9/26/20 5:56 PM	9/28/20 10:30 AM
61005	9/22/20 11:56 AM 9/25/20 11:56 AM
61005	9/26/20 12:25 AM 9/26/20 4:44 PM
61005	10/2/20 1:08 PM	10/5/20 6:11 AM
61005	10/6/20 8:06 AM	10/8/20 7:51 AM
71001	9/4/20 10:00 AM	9/5/20 7:07 AM
71002	9/4/20 12:52 PM	9/6/20 10:28 PM
71002	9/8/20 3:16 AM	9/8/20 5:12 AM
71003	9/6/20 5:13 AM	9/8/20 11:56 AM
71003	9/11/20 5:56 PM	9/11/20 5:59 PM
71004	9/16/20 9:32 AM	9/17/20 9:32 PM
71004	9/23/20 2:20 AM	9/24/20 3:32 AM
71004	9/26/20 5:56 PM	9/26/20 5:58 PM
71005	9/22/20 11:56 AM 9/25/20 11:56 AM
71005	9/26/20 12:25 AM 9/26/20 12:34 AM
71005	10/2/20 1:08 PM	10/5/20 6:11 AM
71005	10/6/20 8:06 AM	10/8/20 7:51 AM
81001	9/4/20 10:00 AM	9/5/20 11:12 AM
81002	9/4/20 12:52 PM	9/4/20 2:22 PM
81002	9/8/20 3:16 AM	9/10/20 5:40 AM
81003	9/6/20 5:13 AM	9/6/20 6:31 AM
81003	9/9/20 12:31 PM	9/10/20 4:36 AM
81004	9/14/20 4:07 AM	9/14/20 5:56 AM
81004	9/20/20 8:55 PM	9/20/20 9:16 PM
81004	9/23/20 11:40 AM 9/25/20 4:14 AM
81005	9/20/20 6:31 AM	9/20/20 7:33 AM
81005	9/20/20 8:01 PM	9/24/20 11:19 AM
81005	9/30/20 7:43 AM	9/30/20 9:27 AM
81005	10/1/20 11:22 AM 10/6/20 9:52 AM
91001	9/4/20 10:00 AM	9/4/20 12:53 PM
91002	9/4/20 12:52 PM	9/6/20 10:28 PM
91002	9/7/20 3:16 AM	9/10/20 5:40 AM
91003	9/6/20 5:13 AM	9/8/20 11:56 AM
91003	9/11/20 5:56 PM	9/12/20 10:01 AM
91004	9/16/20 9:32 AM	9/17/20 9:32 PM
91004	9/23/20 2:20 AM	9/24/20 3:32 AM
91004	9/24/20 8:49 AM	9/28/20 10:30 AM
91005	9/22/20 11:56 AM 9/25/20 11:56 AM
91005	9/26/20 12:25 AM 9/26/20 4:44 PM
91005	10/2/20 1:08 PM	10/5/20 6:11 AM
91005	10/5/20 9:47 AM	10/8/20 7:51 AM
101001	9/4/20 10:00 AM	9/4/20 11:12 PM
101002	9/4/20 12:52 PM	9/6/20 10:28 PM
101002	9/8/20 3:16 AM	9/10/20 5:40 AM
101003	9/6/20 5:13 AM	9/8/20 11:56 AM
101003	9/11/20 5:56 PM	9/12/20 10:01 AM
101004	9/16/20 9:32 AM	9/17/20 9:32 PM
101004	9/23/20 2:20 AM	9/24/20 3:32 AM
101004	9/24/20 8:49 AM	9/28/20 10:30 AM
101005	9/22/20 11:56 AM 9/25/20 11:56 AM
101005	9/26/20 12:25 AM 9/26/20 4:44 PM
101005	10/2/20 1:08 PM	10/5/20 6:11 AM
101005	10/5/20 10:01 AM 10/8/20 7:51 AM
111001	9/4/20 10:00 AM	9/5/20 7:07 AM
111002	9/4/20 12:52 PM	9/8/20 2:19 AM
111002	9/8/20 3:16 AM	9/8/20 5:12 AM
111003	9/6/20 5:13 AM	9/8/20 11:56 AM
111003	9/8/20 4:44 PM	9/11/20 5:59 PM
111004	9/16/20 9:32 AM	9/17/20 9:32 PM
111004	9/23/20 2:20 AM	9/24/20 3:32 AM
111004	9/26/20 5:56 PM	9/26/20 5:58 PM
111005	9/22/20 11:56 AM 9/25/20 11:56 AM
111005	9/26/20 12:25 AM 9/26/20 12:34 AM
111005	10/2/20 1:08 PM	10/5/20 6:11 AM
111005	10/5/20 10:59 AM 10/8/20 7:51 AM
121001	9/4/20 10:00 AM	9/4/20 10:43 PM
121002	9/4/20 12:52 PM	9/4/20 2:22 PM
121002	9/4/20 5:14 PM	9/10/20 5:40 AM
121003	9/6/20 5:13 AM	9/6/20 6:31 AM
121003	9/9/20 12:31 PM	9/10/20 4:36 AM
121004	9/14/20 4:07 AM	9/14/20 5:56 AM
121004	9/19/20 11:19 PM 9/20/20 9:16 PM
121004	9/21/20 2:04 AM	9/25/20 10:57 PM
121005	9/20/20 6:31 AM	9/20/20 7:33 AM
121005	9/20/20 10:11 AM 9/24/20 11:19 AM
121005	9/30/20 7:43 AM	9/30/20 9:27 AM
121005	10/1/20 11:22 AM 10/3/20 11:08 AM
run;

/* For testing to see if the merging of the above two data sets
  can result in the data set FINAL  */
data final; input ID $	hnum	visit	mortality	hadmit datetime16. istar datetime16.istop  datetime16.	hdischarge;  datetime16.
cards;
51001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/4/20 11:12 PM	9/5/20 11:12 AM
51002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/6/20 10:28 PM	9/10/20 12:52 PM
51002	1	2	0	9/4/20 10:57 AM	9/8/20 3:16 AM	9/10/20 5:40 AM	9/10/20 12:52 PM
51003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
51003	2	1	1	9/11/20 4:44 PM	9/11/20 5:56 PM	9/12/20 10:01 AM	9/12/20 10:01 AM
51004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
51004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
51004	2	2	0	9/22/20 2:20 AM	9/26/20 5:56 PM	9/28/20 10:30 AM	9/29/20 5:13 AM
51005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
51005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
51005	2	1	0	10/2/20 12:54 PM	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
51005	2	2	1	10/2/20 12:54 PM	10/6/20 8:06 AM	10/8/20 7:51 AM	10/8/20 7:51 AM
61003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
61004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
61005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
61005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
71001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/5/20 7:07 AM	9/5/20 11:12 AM
71002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/6/20 10:28 PM	9/10/20 12:52 PM
71003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
71004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
71004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
71005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
71005	2	1	0	10/2/20 12:54 PM	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
71005	2	2	0	10/2/20 12:54 PM	10/6/20 8:06 AM	10/8/20 7:51 AM	10/8/20 2:01 PM
81001	1	1	1	9/4/20 10:00 AM	9/4/20 10:00 AM	9/5/20 11:12 AM	9/5/20 11:12 AM
81002	1	1	0	9/4/20 10:57 AM	9/8/20 3:16 AM	9/10/20 5:40 AM	9/10/20 12:52 PM
81003	1	1	0	9/9/20 11:19 AM	9/9/20 12:31 PM	9/10/20 4:36 AM	9/10/20 4:36 AM
81004	1	1	0	9/19/20 8:55 PM	9/23/20 11:40 AM	9/25/20 4:14 AM	9/25/20 10:57 PM
81005	1	1	0	9/20/20 1:43 AM	9/20/20 8:01 PM	9/24/20 11:19 AM	9/24/20 4:07 PM
81005	2	1	1	9/30/20 7:28 AM	10/1/20 11:22 AM	10/6/20 9:52 AM	10/6/20 9:52 AM
91001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/4/20 12:53 PM	9/5/20 11:12 AM
91002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/6/20 10:28 PM	9/10/20 12:52 PM
91002	2	2	0	9/4/20 10:57 AM	9/7/20 3:16 AM	9/10/20 5:40 AM	9/10/20 12:52 PM
91003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
91003	2	1	1	9/11/20 4:44 PM	9/11/20 5:56 PM	9/12/20 10:01 AM	9/12/20 10:01 AM
91004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
91004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
91004	2	2	0	9/22/20 2:20 AM	9/24/20 8:49 AM	9/28/20 10:30 AM	9/29/20 5:13 AM
91005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
91005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
91005	2	1	0	10/2/20 12:54 PM	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
91005	2	2	0	10/2/20 12:54 PM	10/5/20 9:47 AM	10/8/20 7:51 AM	10/8/20 3:18 PM
101003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
101004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
101005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
101005	1	2	0	9/22/20 7:08 AM	9/26/20 12:25 AM	9/26/20 4:44 PM	9/26/20 9:32 PM
111001	1	1	0	9/4/20 10:00 AM	9/4/20 10:00 AM	9/5/20 7:07 AM	9/5/20 11:12 AM
111002	1	1	0	9/4/20 10:57 AM	9/4/20 12:52 PM	9/8/20 2:19 AM	9/10/20 12:52 PM
111003	1	1	0	9/6/20 5:12 AM	9/6/20 5:13 AM	9/8/20 11:56 AM	9/10/20 2:20 AM
111003	2	1	1	9/8/2020 3:00PM	9/8/20 4:44 PM	9/11/20 5:59 PM	9/11/20 5:59 PM
111004	1	1	0	9/15/20 9:32 PM	9/16/20 9:32 AM	9/17/20 9:32 PM	9/18/20 9:39 PM
111004	2	1	0	9/22/20 2:20 AM	9/23/20 2:20 AM	9/24/20 3:32 AM	9/29/20 5:13 AM
111005	1	1	0	9/22/20 7:08 AM	9/22/20 11:56 AM	9/25/20 11:56 AM	9/26/20 9:32 PM
111005	2	1	0	10/2/20 12:54 PM	10/2/20 1:08 PM	10/5/20 6:11 AM	10/8/20 3:18 PM
111005	2	2	0	10/2/20 12:54 PM	10/5/20 10:59 AM	10/8/20 7:51 AM	10/8/20 3:18 PM
121002	1	1	0	9/4/20 10:57 AM	9/4/20 1:50 PM	9/10/20 5:40 AM	9/10/20 12:52 PM
121003	1	1	0	9/9/20 11:19 AM	9/9/20 12:31 PM	9/10/20 4:36 AM	9/10/20 4:36 AM
121004	1	1	0	9/19/20 8:55 PM	9/19/20 11:19 PM	9/20/20 9:16 PM	9/25/20 10:57 PM
121004	1	2	1	9/19/20 8:55 PM	9/21/20 2:04 AM	9/25/20 10:57 PM	9/25/20 10:57 PM
121005	1	1	0	9/20/20 1:43 AM	9/20/20 10:11 AM	9/24/20 11:19 AM	9/24/20 4:07 PM
121005	2	1	0	9/30/20 7:28 AM	10/1/20 11:22 AM	10/3/20 11:08 AM	10/6/20 9:52 AM
run;

Any help you can provide is GREATLY appreciated.

Andrew

Re: Difficult MERGE problem that Technical Support couldn't answer

PaigeMiller — Thu, 24 Sep 2020 17:46:27 GMT

Formats are not relevant to MERGE problems. I will take a look at it.

Re: Difficult MERGE problem that Technical Support couldn't answer

PaigeMiller — Thu, 24 Sep 2020 19:00:20 GMT

Why are there duplicate admission and discharge times here? Can I just use the 2nd one in each case?

111005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
111005	9/22/20 7:08 AM	9/26/20 9:32 PM	0
111005	10/2/20 12:54 PM 10/8/20 3:18 PM  0
111005	10/2/20 12:54 PM 10/8/20 3:18 PM 1

Re: Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Thu, 24 Sep 2020 19:24:47 GMT

If a patient has duplicate hadmit & hdischarge times, then use the 2nd one. Thanks!

Re: Difficult MERGE problem that Technical Support couldn't answer

PaigeMiller — Thu, 24 Sep 2020 19:28:56 GMT

Okay. Go it.

I think part of your issue is that you have defined this as a MERGE problem instead of a programming problem. I think it would be difficult if not impossible to do all of this as a single merge, so here is what I have so far. From the data set in my code named BOTH1, you should be able to figure out which of the three possibilities each patient falls into (point 5 in your original message).

data main; 
infile cards expandtabs;
input ID $	hadmit & mdyampm16.	hdischarge & :mdyampm16.	mortality;
cards;
51001	9/4/20 10:00 am  	9/5/20 11:12 am  	0
51002	9/4/20 10:57 am  	9/10/20 12:52 pm   0
51002	9/4/20 10:57 am  	9/10/20 12:52 pm   1
51003	9/6/20 5:12 am  	9/10/20 2:20 am  	0
51003	9/11/20 4:44 pm  	9/12/20 10:01 am   1
51004	9/15/20 9:32 pm  	9/18/20 9:39 pm  	0
51004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
51004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
51005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
51005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
51005	10/2/20 12:54 pm   10/8/20 3:18 pm   0
51005	10/2/20 12:54 pm   10/8/20 7:51 am   1
61001	9/4/20 10:00 am  	 .	0
61002	9/4/20 10:57 am  	 .	0
61002	9/4/20 10:57 am  	 .	0
61003	9/6/20 5:12 am  	9/10/20 2:20 am  	0
61003	9/11/20 4:44 pm  	 .	1
61004	9/15/20 9:32 pm  	9/18/20 9:39 pm  	0
61004	9/22/20 2:20 am  	 .	0
61004	9/22/20 2:20 am  	.	0
61005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
61005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
61005	10/2/20 12:54 pm    .	0
61005	10/2/20 12:54 pm  	.	1
71001	9/4/20 10:00 am  	9/5/20 11:12 am  	0
71002	9/4/20 10:57 am  	9/10/20 12:52 pm  	0
71002	9/4/20 10:57 am  	9/10/20 12:52 pm  	0
71003	9/6/20 5:12 am  	9/10/20 2:20 am  	0
71003	9/11/20 4:44 pm  	9/11/20 5:59 pm  	1
71004	9/15/20 9:32 pm  	9/18/20 9:39 pm  	0
71004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
71004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
71005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
71005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
71005	10/2/20 12:54 pm   10/8/20 3:18 pm   0
71005	10/2/20 12:54 pm   10/8/20 3:18 pm   1
81001	9/4/20 10:00 am  	9/5/20 11:12 am  	1
81002	9/4/20 10:57 am  	9/10/20 12:52 pm   0
81002	9/4/20 10:57 am  	9/10/20 12:52 pm   1
81003	9/6/20 5:12 am  	9/7/20 8:55 pm  	0
81003	9/9/20 11:19 am  	9/10/20 4:36 am  	0
81004	9/13/20 4:07 pm  	9/15/20 6:03 am  	0
81004	9/19/20 8:55 pm  	9/25/20 10:57 pm   0
81004	9/19/20 8:55 pm  	9/25/20 10:57 pm   1
81005	9/20/20 1:43 am  	9/24/20 4:07 pm  	0
81005	9/20/20 1:43 am  	9/24/20 4:07 pm  	0
81005	9/30/20 7:28 am  	10/6/20 9:52 am  	0
81005	9/30/20 7:28 am  	10/6/20 9:52 am  	1
91001	9/4/20 10:00 am  	9/5/20 11:12 am  	0
91002	9/4/20 10:57 am  	9/10/20 12:52 pm   0
91002	9/4/20 10:57 am  	9/10/20 12:52 pm   1
91003	9/6/20 5:12 am  	9/10/20 2:20 am  	0
91003	9/11/20 4:44 pm  	9/12/20 10:01 am   1
91004	9/15/20 9:32 pm  	9/18/20 9:39 pm  	0
91004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
91004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
91005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
91005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
91005	10/2/20 12:54 pm   10/8/20 3:18 pm   0
91005	10/2/20 12:54 pm   10/8/20 3:18 pm   1
101001	9/4/20 10:00 am  	 .	0
101002	9/4/20 10:57 am  	 .	0
101002	9/4/20 10:57 am  	 .	0
101003	9/6/20 5:12 am  	9/10/20 2:20 am  	0
101003	9/11/20 4:44 pm  	.	1
101004	9/15/20 9:32 pm  	9/18/20 9:39 pm  	0
101004	9/22/20 2:20 am  	.	0
101004	9/22/20 2:20 am  	.	0
101005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
101005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
101005	10/2/20 12:54 pm  	.	0
101005	10/2/20 12:54 pm  	.	1
111001	9/4/20 10:00 am  	9/5/20 11:12 am  	0
111002	9/4/20 10:57 am  	9/10/20 12:52 pm   0
111002	9/4/20 10:57 am  	9/10/20 12:52 pm   0
111003	9/6/20 5:12 am  	9/10/20 2:20 am  	0
111003	9/8/2020 3:00pm  	9/11/20 5:59 pm  	1
111004	9/15/20 9:32 pm  	9/18/20 9:39 pm  	0
111004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
111004	9/22/20 2:20 am  	9/29/20 5:13 am  	0
111005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
111005	9/22/20 7:08 am  	9/26/20 9:32 pm  	0
111005	10/2/20 12:54 pm   10/8/20 3:18 pm    0
111005	10/2/20 12:54 pm   10/8/20 3:18 pm   1
121001	9/4/20 10:00 am  	9/5/20 11:12 am  	1
121002	9/4/20 10:57 am  	9/10/20 12:52 pm   0
121002	9/4/20 10:57 am  	9/10/20 12:52 pm   1
121003	9/6/20 5:12 am  	9/7/20 8:55 pm  	0
121003	9/9/20 11:19 am  	9/10/20 4:36 am  	0
121004	9/13/20 4:07 pm  	9/15/20 6:03 am  	0
121004	9/19/20 8:55 pm  	9/25/20 10:57 pm   0
121004	9/19/20 8:55 pm  	9/25/20 10:57 pm    1
121005	9/20/20 1:43 am  	9/24/20 4:07 pm  	0
121005	9/20/20 1:43 am  	9/24/20 4:07 pm  	0
121005	9/30/20 7:28 am  	10/6/20 9:52 am  	0
121005	9/30/20 7:28 am  	10/6/20 9:52 am  	1
;
run;

data vitals; 
input ID $	istart & mdyampm16.	istop & mdyampm16.;
cards;
51001	9/4/20 10:00 am  	9/4/20 11:12 pm  
51002	9/4/20 12:52 pm  	9/6/20 10:28 pm  
51002	9/8/20 3:16 am  	9/10/20 5:40 am  
51003	9/6/20 5:13 am  	9/8/20 11:56 am  
51003	9/11/20 5:56 pm  	9/12/20 10:01 am  
51004	9/16/20 9:32 am  	9/17/20 9:32 pm  
51004	9/23/20 2:20 am  	9/24/20 3:32 am  
51004	9/26/20 5:56 pm  	9/28/20 10:30 am  
51005	9/22/20 11:56 am   9/25/20 11:56 am  
51005	9/26/20 12:25 am   9/26/20 4:44 pm  
51005	10/2/20 1:08 pm  	10/5/20 6:11 am  
51005	10/6/20 8:06 am  	10/8/20 7:51 am  
61001	9/4/20 10:00 am  	9/4/20 11:12 pm  
61002	9/4/20 12:52 pm  	9/6/20 10:28 pm  
61002	9/8/20 3:16 am  	9/10/20 5:40 am  
61003	9/6/20 5:13 am  	9/8/20 11:56 am  
61003	9/11/20 5:56 pm  	9/12/20 10:01 am  
61004	9/16/20 9:32 am  	9/17/20 9:32 pm  
61004	9/23/20 2:20 am  	9/24/20 3:32 am  
61004	9/26/20 5:56 pm  	9/28/20 10:30 am  
61005	9/22/20 11:56 am   9/25/20 11:56 am  
61005	9/26/20 12:25 am   9/26/20 4:44 pm  
61005	10/2/20 1:08 pm  	10/5/20 6:11 am  
61005	10/6/20 8:06 am  	10/8/20 7:51 am  
71001	9/4/20 10:00 am  	9/5/20 7:07 am  
71002	9/4/20 12:52 pm  	9/6/20 10:28 pm  
71002	9/8/20 3:16 am  	9/8/20 5:12 am  
71003	9/6/20 5:13 am  	9/8/20 11:56 am  
71003	9/11/20 5:56 pm  	9/11/20 5:59 pm  
71004	9/16/20 9:32 am  	9/17/20 9:32 pm  
71004	9/23/20 2:20 am  	9/24/20 3:32 am  
71004	9/26/20 5:56 pm  	9/26/20 5:58 pm  
71005	9/22/20 11:56 am   9/25/20 11:56 am  
71005	9/26/20 12:25 am   9/26/20 12:34 am  
71005	10/2/20 1:08 pm  	10/5/20 6:11 am  
71005	10/6/20 8:06 am  	10/8/20 7:51 am  
81001	9/4/20 10:00 am  	9/5/20 11:12 am  
81002	9/4/20 12:52 pm  	9/4/20 2:22 pm  
81002	9/8/20 3:16 am  	9/10/20 5:40 am  
81003	9/6/20 5:13 am  	9/6/20 6:31 am  
81003	9/9/20 12:31 pm  	9/10/20 4:36 am  
81004	9/14/20 4:07 am  	9/14/20 5:56 am  
81004	9/20/20 8:55 pm  	9/20/20 9:16 pm  
81004	9/23/20 11:40 am   9/25/20 4:14 am  
81005	9/20/20 6:31 am  	9/20/20 7:33 am  
81005	9/20/20 8:01 pm  	9/24/20 11:19 am  
81005	9/30/20 7:43 am  	9/30/20 9:27 am  
81005	10/1/20 11:22 am   10/6/20 9:52 am  
91001	9/4/20 10:00 am  	9/4/20 12:53 pm  
91002	9/4/20 12:52 pm  	9/6/20 10:28 pm  
91002	9/7/20 3:16 am  	9/10/20 5:40 am  
91003	9/6/20 5:13 am  	9/8/20 11:56 am  
91003	9/11/20 5:56 pm  	9/12/20 10:01 am  
91004	9/16/20 9:32 am  	9/17/20 9:32 pm  
91004	9/23/20 2:20 am  	9/24/20 3:32 am  
91004	9/24/20 8:49 am  	9/28/20 10:30 am  
91005	9/22/20 11:56 am   9/25/20 11:56 am  
91005	9/26/20 12:25 am   9/26/20 4:44 pm  
91005	10/2/20 1:08 pm  	10/5/20 6:11 am  
91005	10/5/20 9:47 am  	10/8/20 7:51 am  
101001	9/4/20 10:00 am  	9/4/20 11:12 pm  
101002	9/4/20 12:52 pm  	9/6/20 10:28 pm  
101002	9/8/20 3:16 am  	9/10/20 5:40 am  
101003	9/6/20 5:13 am  	9/8/20 11:56 am  
101003	9/11/20 5:56 pm  	9/12/20 10:01 am  
101004	9/16/20 9:32 am  	9/17/20 9:32 pm  
101004	9/23/20 2:20 am  	9/24/20 3:32 am  
101004	9/24/20 8:49 am  	9/28/20 10:30 am  
101005	9/22/20 11:56 am   9/25/20 11:56 am  
101005	9/26/20 12:25 am   9/26/20 4:44 pm  
101005	10/2/20 1:08 pm  	10/5/20 6:11 am  
101005	10/5/20 10:01 am   10/8/20 7:51 am  
111001	9/4/20 10:00 am  	9/5/20 7:07 am  
111002	9/4/20 12:52 pm  	9/8/20 2:19 am  
111002	9/8/20 3:16 am  	9/8/20 5:12 am  
111003	9/6/20 5:13 am  	9/8/20 11:56 am  
111003	9/8/20 4:44 pm  	9/11/20 5:59 pm  
111004	9/16/20 9:32 am  	9/17/20 9:32 pm  
111004	9/23/20 2:20 am  	9/24/20 3:32 am  
111004	9/26/20 5:56 pm  	9/26/20 5:58 pm  
111005	9/22/20 11:56 am   9/25/20 11:56 am  
111005	9/26/20 12:25 am   9/26/20 12:34 am  
111005	10/2/20 1:08 pm  	10/5/20 6:11 am  
111005	10/5/20 10:59 am   10/8/20 7:51 am  
121001	9/4/20 10:00 am  	9/4/20 10:43 pm  
121002	9/4/20 12:52 pm  	9/4/20 2:22 pm  
121002	9/4/20 5:14 pm  	9/10/20 5:40 am  
121003	9/6/20 5:13 am  	9/6/20 6:31 am  
121003	9/9/20 12:31 pm  	9/10/20 4:36 am  
121004	9/14/20 4:07 am  	9/14/20 5:56 am  
121004	9/19/20 11:19 pm   9/20/20 9:16 pm  
121004	9/21/20 2:04 am  	9/25/20 10:57 pm  
121005	9/20/20 6:31 am  	9/20/20 7:33 am  
121005	9/20/20 10:11 am   9/24/20 11:19 am  
121005	9/30/20 7:43 am  	9/30/20 9:27 am  
121005	10/1/20 11:22 am   10/3/20 11:08 am  
run;

proc sort data=main; by id hadmit; run;
data main1;
	set main;
	by id hadmit;
	/* Delete hospital visits with no discharge time */
	if missing(hdischarge) then delete;
	/* Delete hospital visits less than 2 hours */
	if (hdischarge-hadmit)<2*60*60 then delete;
	if not last.hadmit then delete;
run;
proc sort data=vitals; by id istart; run;
data vitals1;
	set vitals;
	by id istart;
	/* Delete ICU visits less than 2 hours */
	if (istop-istart)<2*60*60 then delete;
	if last.istart then output;
run;

/* Merge */
proc sql;
	create table both as select distinct m.*,v.istart,v.istop
		from main1 as m left join vitals1 as v 
		on m.id=v.id and m.hadmit<=v.istart<=m.hdischarge
		order by m.id,m.hadmit,v.istart;
quit;

/* Number sequentially */
data both1;
	set both;
	by id hadmit istart;
	if first.id then patient_visit=0;
	if first.hadmit then patient_visit+1;
	if first.hadmit then icu_visit=0;
	icu_visit+1;
	format hadmit hdischarge istart istop datetime16.;
run;

Re: Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Thu, 24 Sep 2020 20:58:40 GMT

@PaigeMiller

There's just a few patients in both1 that look like they don't exactly match my FINAL data set. I'm going to take a look at this tomorrow morning and try to figure out why.

Re: Difficult MERGE problem that Technical Support couldn't answer

mkeintz — Fri, 25 Sep 2020 03:58:41 GMT

I do see this as a merge problem. But before I offer some code, I have a question about your criteria.

You want one output record per qualifying ICU visit record. But you want to delete ICU visits < 2 hrs. ~~[edited addition] Does that also mean that such ICU visits do not get a sequence number?~~[Oops, I see you answered this in your comments, but my question below still holds]

So if a hospitalization had, say, only a single ICU visit and that visit took < 2 hrs, I take that to mean that you don't want any output record corresponding to that hospitalization.

Yet elsewhere you apparently want to output mortality events even in that case. So what does the corresponding output record look like? Does being the last ICU visit for a hospitalization with a mortality event mean it always qualifies for the FINAL dataset, even if it is less than 2 hours?

Re: Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Fri, 25 Sep 2020 14:33:29 GMT

@PaigeMiller I think I see the gap. ICU visits < 2 hours don't get included. So patients 81003 and 81004 have only one hospitalization that qualifies, and that should be = 1 not 2 (even though theoretically it's the second hospitalization. The same holds true for patients 121003 and 121004.

Re: Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Fri, 25 Sep 2020 14:42:40 GMT

@mkeintz Every ICU visit < 2 hrs. gets eliminated, as if it never occurred. So if a patient mortality occurs during that excluded ICU visit, it's as if it didn't occur. The hospitalization (if that was the only ICU visit) also doesn't get counted.

Thanks!

Andrew

Re: Difficult MERGE problem that Technical Support couldn't answer

DocMartin — Fri, 25 Sep 2020 18:57:02 GMT

@PaigeMiller O.K. There's one line of code that I inserted and everything works. It was:

data both1a; set both; if istart = . then delete; run;

That was right before the "number sequentially" comment.

Thanks!