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:
Thank you.
Andrew Kramer
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/
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
Formats are not relevant to MERGE problems. I will take a look at it.
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
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;
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.
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?
@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
@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.
@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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.