cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

Difficult MERGE problem that Technical Support couldn't answer

Posted 09-24-2020 12:24 PM (856 views)

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       
IDhadmithdischargemortality IDistartistop IDhnumvisitmortalityhadmitistartistophdischarge
510019/4/20 10:00 AM9/5/20 11:12 AM0 510019/4/20 10:00 AM9/4/20 11:12 PM 510011109/4/20 10:00 AM9/4/20 10:00 AM9/4/20 11:12 PM9/5/20 11:12 AM
510029/4/20 10:57 AM9/10/20 12:52 PM0 510029/4/20 12:52 PM9/6/20 10:28 PM 510021109/4/20 10:57 AM9/4/20 12:52 PM9/6/20 10:28 PM9/10/20 12:52 PM
510029/4/20 10:57 AM9/10/20 12:52 PM1 510029/8/20 3:16 AM9/10/20 5:40 AM 510021209/4/20 10:57 AM9/8/20 3:16 AM9/10/20 5:40 AM9/10/20 12:52 PM
510039/6/20 5:12 AM9/10/20 2:20 AM0 510039/6/20 5:13 AM9/8/20 11:56 AM 510031109/6/20 5:12 AM9/6/20 5:13 AM9/8/20 11:56 AM9/10/20 2:20 AM
510039/11/20 4:44 PM9/12/20 10:01 AM1 510039/11/20 5:56 PM9/12/20 10:01 AM 510032119/11/20 4:44 PM9/11/20 5:56 PM9/12/20 10:01 AM9/12/20 10:01 AM
510049/15/20 9:32 PM9/18/20 9:39 PM0 510049/16/20 9:32 AM9/17/20 9:32 PM 510041109/15/20 9:32 PM9/16/20 9:32 AM9/17/20 9:32 PM9/18/20 9:39 PM
510049/22/20 2:20 AM9/29/20 5:13 AM0 510049/23/20 2:20 AM9/24/20 3:32 AM 510042109/22/20 2:20 AM9/23/20 2:20 AM9/24/20 3:32 AM9/29/20 5:13 AM
510049/22/20 2:20 AM9/29/20 5:13 AM0 510049/26/20 5:56 PM9/28/20 10:30 AM 510042209/22/20 2:20 AM9/26/20 5:56 PM9/28/20 10:30 AM9/29/20 5:13 AM
510059/22/20 7:08 AM9/26/20 9:32 PM0 510059/22/20 11:56 AM9/25/20 11:56 AM 510051109/22/20 7:08 AM9/22/20 11:56 AM9/25/20 11:56 AM9/26/20 9:32 PM
510059/22/20 7:08 AM9/26/20 9:32 PM0 510059/26/20 12:25 AM9/26/20 4:44 PM 510051209/22/20 7:08 AM9/26/20 12:25 AM9/26/20 4:44 PM9/26/20 9:32 PM
51005##############10/8/20 3:18 PM0 5100510/2/20 1:08 PM10/5/20 6:11 AM 51005210##############10/2/20 1:08 PM10/5/20 6:11 AM10/8/20 3:18 PM
51005##############10/8/20 7:51 AM1 5100510/6/20 8:06 AM10/8/20 7:51 AM 51005221##############10/6/20 8:06 AM10/8/20 7:51 AM10/8/20 7:51 AM
610019/4/20 10:00 AM 0 610019/4/20 10:00 AM9/4/20 11:12 PM 610031109/6/20 5:12 AM9/6/20 5:13 AM9/8/20 11:56 AM9/10/20 2:20 AM
610029/4/20 10:57 AM 0 610029/4/20 12:52 PM9/6/20 10:28 PM 610041109/15/20 9:32 PM9/16/20 9:32 AM9/17/20 9:32 PM9/18/20 9:39 PM
610029/4/20 10:57 AM 0 610029/8/20 3:16 AM9/10/20 5:40 AM 610051109/22/20 7:08 AM9/22/20 11:56 AM9/25/20 11:56 AM9/26/20 9:32 PM
610039/6/20 5:12 AM9/10/20 2:20 AM0 610039/6/20 5:13 AM9/8/20 11:56 AM 610051209/22/20 7:08 AM9/26/20 12:25 AM9/26/20 4:44 PM9/26/20 9:32 PM
610039/11/20 4:44 PM 1 610039/11/20 5:56 PM9/12/20 10:01 AM 710011109/4/20 10:00 AM9/4/20 10:00 AM9/5/20 7:07 AM9/5/20 11:12 AM
610049/15/20 9:32 PM9/18/20 9:39 PM0 610049/16/20 9:32 AM9/17/20 9:32 PM 710021109/4/20 10:57 AM9/4/20 12:52 PM9/6/20 10:28 PM9/10/20 12:52 PM
610049/22/20 2:20 AM 0 610049/23/20 2:20 AM9/24/20 3:32 AM 710031109/6/20 5:12 AM9/6/20 5:13 AM9/8/20 11:56 AM9/10/20 2:20 AM
610049/22/20 2:20 AM 0 610049/26/20 5:56 PM9/28/20 10:30 AM 710041109/15/20 9:32 PM9/16/20 9:32 AM9/17/20 9:32 PM9/18/20 9:39 PM
610059/22/20 7:08 AM9/26/20 9:32 PM0 610059/22/20 11:56 AM9/25/20 11:56 AM 710042109/22/20 2:20 AM9/23/20 2:20 AM9/24/20 3:32 AM9/29/20 5:13 AM
610059/22/20 7:08 AM9/26/20 9:32 PM0 610059/26/20 12:25 AM9/26/20 4:44 PM 710051109/22/20 7:08 AM9/22/20 11:56 AM9/25/20 11:56 AM9/26/20 9:32 PM
61005############## 0 6100510/2/20 1:08 PM10/5/20 6:11 AM 71005210##############10/2/20 1:08 PM10/5/20 6:11 AM10/8/20 3:18 PM
61005############## 1 6100510/6/20 8:06 AM10/8/20 7:51 AM 71005220##############10/6/20 8:06 AM10/8/20 7:51 AM10/8/20 2:01 PM
710019/4/20 10:00 AM9/5/20 11:12 AM0 710019/4/20 10:00 AM9/5/20 7:07 AM 810011119/4/20 10:00 AM9/4/20 10:00 AM9/5/20 11:12 AM9/5/20 11:12 AM
710029/4/20 10:57 AM9/10/20 12:52 PM0 710029/4/20 12:52 PM9/6/20 10:28 PM 810021109/4/20 10:57 AM9/8/20 3:16 AM9/10/20 5:40 AM9/10/20 12:52 PM
710029/4/20 10:57 AM9/10/20 12:52 PM0 710029/8/20 3:16 AM9/8/20 5:12 AM 810031109/9/20 11:19 AM9/9/20 12:31 PM9/10/20 4:36 AM9/10/20 4:36 AM
710039/6/20 5:12 AM9/10/20 2:20 AM0 710039/6/20 5:13 AM9/8/20 11:56 AM 810041109/19/20 8:55 PM9/23/20 11:40 AM9/25/20 4:14 AM9/25/20 10:57 PM
710039/11/20 4:44 PM9/11/20 5:59 PM1 710039/11/20 5:56 PM9/11/20 5:59 PM 810051109/20/20 1:43 AM9/20/20 8:01 PM9/24/20 11:19 AM9/24/20 4:07 PM
710049/15/20 9:32 PM9/18/20 9:39 PM0 710049/16/20 9:32 AM9/17/20 9:32 PM 810052119/30/20 7:28 AM10/1/20 11:22 AM10/6/20 9:52 AM10/6/20 9:52 AM
710049/22/20 2:20 AM9/29/20 5:13 AM0 710049/23/20 2:20 AM9/24/20 3:32 AM 910011109/4/20 10:00 AM9/4/20 10:00 AM9/4/20 12:53 PM9/5/20 11:12 AM
710049/22/20 2:20 AM9/29/20 5:13 AM0 710049/26/20 5:56 PM9/26/20 5:58 PM 910021109/4/20 10:57 AM9/4/20 12:52 PM9/6/20 10:28 PM9/10/20 12:52 PM
710059/22/20 7:08 AM9/26/20 9:32 PM0 710059/22/20 11:56 AM9/25/20 11:56 AM 910022209/4/20 10:57 AM9/7/20 3:16 AM9/10/20 5:40 AM9/10/20 12:52 PM
710059/22/20 7:08 AM9/26/20 9:32 PM0 710059/26/20 12:25 AM9/26/20 12:34 AM 910031109/6/20 5:12 AM9/6/20 5:13 AM9/8/20 11:56 AM9/10/20 2:20 AM
71005##############10/8/20 3:18 PM0 7100510/2/20 1:08 PM10/5/20 6:11 AM 910032119/11/20 4:44 PM9/11/20 5:56 PM9/12/20 10:01 AM9/12/20 10:01 AM
71005##############10/8/20 3:18 PM1 7100510/6/20 8:06 AM10/8/20 7:51 AM 910041109/15/20 9:32 PM9/16/20 9:32 AM9/17/20 9:32 PM9/18/20 9:39 PM
810019/4/20 10:00 AM9/5/20 11:12 AM1 810019/4/20 10:00 AM9/5/20 11:12 AM 910042109/22/20 2:20 AM9/23/20 2:20 AM9/24/20 3:32 AM9/29/20 5:13 AM
810029/4/20 10:57 AM9/10/20 12:52 PM0 810029/4/20 12:52 PM9/4/20 2:22 PM 910042209/22/20 2:20 AM9/24/20 8:49 AM9/28/20 10:30 AM9/29/20 5:13 AM
810029/4/20 10:57 AM9/10/20 12:52 PM1 810029/8/20 3:16 AM9/10/20 5:40 AM 910051109/22/20 7:08 AM9/22/20 11:56 AM9/25/20 11:56 AM9/26/20 9:32 PM
810039/6/20 5:12 AM9/7/20 8:55 PM0 810039/6/20 5:13 AM9/6/20 6:31 AM 910051209/22/20 7:08 AM9/26/20 12:25 AM9/26/20 4:44 PM9/26/20 9:32 PM
810039/9/20 11:19 AM9/10/20 4:36 AM0 810039/9/20 12:31 PM9/10/20 4:36 AM 91005210##############10/2/20 1:08 PM10/5/20 6:11 AM10/8/20 3:18 PM
810049/13/20 4:07 PM9/15/20 6:03 AM0 810049/14/20 4:07 AM9/14/20 5:56 AM 91005220##############10/5/20 9:47 AM10/8/20 7:51 AM10/8/20 3:18 PM
810049/19/20 8:55 PM9/25/20 10:57 PM0 810049/20/20 8:55 PM9/20/20 9:16 PM 1010031109/6/20 5:12 AM9/6/20 5:13 AM9/8/20 11:56 AM9/10/20 2:20 AM
810049/19/20 8:55 PM9/25/20 10:57 PM1 810049/23/20 11:40 AM9/25/20 4:14 AM 1010041109/15/20 9:32 PM9/16/20 9:32 AM9/17/20 9:32 PM9/18/20 9:39 PM
810059/20/20 1:43 AM9/24/20 4:07 PM0 810059/20/20 6:31 AM9/20/20 7:33 AM 1010051109/22/20 7:08 AM9/22/20 11:56 AM9/25/20 11:56 AM9/26/20 9:32 PM
810059/20/20 1:43 AM9/24/20 4:07 PM0 810059/20/20 8:01 PM9/24/20 11:19 AM 1010051209/22/20 7:08 AM9/26/20 12:25 AM9/26/20 4:44 PM9/26/20 9:32 PM
810059/30/20 7:28 AM10/6/20 9:52 AM0 810059/30/20 7:43 AM9/30/20 9:27 AM 1110011109/4/20 10:00 AM9/4/20 10:00 AM9/5/20 7:07 AM9/5/20 11:12 AM
810059/30/20 7:28 AM10/6/20 9:52 AM1 8100510/1/20 11:22 AM10/6/20 9:52 AM 1110021109/4/20 10:57 AM9/4/20 12:52 PM9/8/20 2:19 AM9/10/20 12:52 PM
910019/4/20 10:00 AM9/5/20 11:12 AM0 910019/4/20 10:00 AM9/4/20 12:53 PM 1110031109/6/20 5:12 AM9/6/20 5:13 AM9/8/20 11:56 AM9/10/20 2:20 AM
910029/4/20 10:57 AM9/10/20 12:52 PM0 910029/4/20 12:52 PM9/6/20 10:28 PM 1110032119/8/2020 3:00PM9/8/20 4:44 PM9/11/20 5:59 PM9/11/20 5:59 PM
910029/4/20 10:57 AM9/10/20 12:52 PM1 910029/7/20 3:16 AM9/10/20 5:40 AM 1110041109/15/20 9:32 PM9/16/20 9:32 AM9/17/20 9:32 PM9/18/20 9:39 PM
910039/6/20 5:12 AM9/10/20 2:20 AM0 910039/6/20 5:13 AM9/8/20 11:56 AM 1110042109/22/20 2:20 AM9/23/20 2:20 AM9/24/20 3:32 AM9/29/20 5:13 AM
910039/11/20 4:44 PM9/12/20 10:01 AM1 910039/11/20 5:56 PM9/12/20 10:01 AM 1110051109/22/20 7:08 AM9/22/20 11:56 AM9/25/20 11:56 AM9/26/20 9:32 PM
910049/15/20 9:32 PM9/18/20 9:39 PM0 910049/16/20 9:32 AM9/17/20 9:32 PM 111005210##############10/2/20 1:08 PM10/5/20 6:11 AM10/8/20 3:18 PM
910049/22/20 2:20 AM9/29/20 5:13 AM0 910049/23/20 2:20 AM9/24/20 3:32 AM 111005220##############10/5/20 10:59 AM10/8/20 7:51 AM10/8/20 3:18 PM
910049/22/20 2:20 AM9/29/20 5:13 AM0 910049/24/20 8:49 AM9/28/20 10:30 AM 1210021109/4/20 10:57 AM9/4/20 1:50 PM9/10/20 5:40 AM9/10/20 12:52 PM
910059/22/20 7:08 AM9/26/20 9:32 PM0 910059/22/20 11:56 AM9/25/20 11:56 AM 1210031109/9/20 11:19 AM9/9/20 12:31 PM9/10/20 4:36 AM9/10/20 4:36 AM
910059/22/20 7:08 AM9/26/20 9:32 PM0 910059/26/20 12:25 AM9/26/20 4:44 PM 1210041109/19/20 8:55 PM9/19/20 11:19 PM9/20/20 9:16 PM9/25/20 10:57 PM
91005##############10/8/20 3:18 PM0 9100510/2/20 1:08 PM10/5/20 6:11 AM 1210041219/19/20 8:55 PM9/21/20 2:04 AM9/25/20 10:57 PM9/25/20 10:57 PM
91005##############10/8/20 3:18 PM1 9100510/5/20 9:47 AM10/8/20 7:51 AM 1210051109/20/20 1:43 AM9/20/20 10:11 AM9/24/20 11:19 AM9/24/20 4:07 PM
1010019/4/20 10:00 AM 0 1010019/4/20 10:00 AM9/4/20 11:12 PM 1210052109/30/20 7:28 AM10/1/20 11:22 AM10/3/20 11:08 AM10/6/20 9:52 AM
1010029/4/20 10:57 AM 0 1010029/4/20 12:52 PM9/6/20 10:28 PM         
1010029/4/20 10:57 AM 0 1010029/8/20 3:16 AM9/10/20 5:40 AM         
1010039/6/20 5:12 AM9/10/20 2:20 AM0 1010039/6/20 5:13 AM9/8/20 11:56 AM         
1010039/11/20 4:44 PM 1 1010039/11/20 5:56 PM9/12/20 10:01 AM         
1010049/15/20 9:32 PM9/18/20 9:39 PM0 1010049/16/20 9:32 AM9/17/20 9:32 PM         
1010049/22/20 2:20 AM 0 1010049/23/20 2:20 AM9/24/20 3:32 AM         
1010049/22/20 2:20 AM 0 1010049/24/20 8:49 AM9/28/20 10:30 AM         
1010059/22/20 7:08 AM9/26/20 9:32 PM0 1010059/22/20 11:56 AM9/25/20 11:56 AM         
1010059/22/20 7:08 AM9/26/20 9:32 PM0 1010059/26/20 12:25 AM9/26/20 4:44 PM         
101005############## 0 10100510/2/20 1:08 PM10/5/20 6:11 AM         
101005############## 1 10100510/5/20 10:01 AM10/8/20 7:51 AM         
1110019/4/20 10:00 AM9/5/20 11:12 AM0 1110019/4/20 10:00 AM9/5/20 7:07 AM         
1110029/4/20 10:57 AM9/10/20 12:52 PM0 1110029/4/20 12:52 PM9/8/20 2:19 AM         
1110029/4/20 10:57 AM9/10/20 12:52 PM0 1110029/8/20 3:16 AM9/8/20 5:12 AM         
1110039/6/20 5:12 AM9/10/20 2:20 AM0 1110039/6/20 5:13 AM9/8/20 11:56 AM         
1110039/8/2020 3:00PM9/11/20 5:59 PM1 1110039/8/20 4:44 PM9/11/20 5:59 PM         
1110049/15/20 9:32 PM9/18/20 9:39 PM0 1110049/16/20 9:32 AM9/17/20 9:32 PM         
1110049/22/20 2:20 AM9/29/20 5:13 AM0 1110049/23/20 2:20 AM9/24/20 3:32 AM         
1110049/22/20 2:20 AM9/29/20 5:13 AM0 1110049/26/20 5:56 PM9/26/20 5:58 PM         
1110059/22/20 7:08 AM9/26/20 9:32 PM0 1110059/22/20 11:56 AM9/25/20 11:56 AM         
1110059/22/20 7:08 AM9/26/20 9:32 PM0 1110059/26/20 12:25 AM9/26/20 12:34 AM         
111005##############10/8/20 3:18 PM0 11100510/2/20 1:08 PM10/5/20 6:11 AM         
111005##############10/8/20 3:18 PM1 11100510/5/20 10:59 AM10/8/20 7:51 AM         
1210019/4/20 10:00 AM9/5/20 11:12 AM1 1210019/4/20 10:00 AM9/4/20 10:43 PM         
1210029/4/20 10:57 AM9/10/20 12:52 PM0 1210029/4/20 12:52 PM9/4/20 2:22 PM         
1210029/4/20 10:57 AM9/10/20 12:52 PM1 1210029/4/20 5:14 PM9/10/20 5:40 AM         
1210039/6/20 5:12 AM9/7/20 8:55 PM0 1210039/6/20 5:13 AM9/6/20 6:31 AM         
1210039/9/20 11:19 AM9/10/20 4:36 AM0 1210039/9/20 12:31 PM9/10/20 4:36 AM         
1210049/13/20 4:07 PM9/15/20 6:03 AM0 1210049/14/20 4:07 AM9/14/20 5:56 AM         
1210049/19/20 8:55 PM9/25/20 10:57 PM0 1210049/19/20 11:19 PM9/20/20 9:16 PM         
1210049/19/20 8:55 PM9/25/20 10:57 PM1 1210049/21/20 2:04 AM9/25/20 10:57 PM         
1210059/20/20 1:43 AM9/24/20 4:07 PM0 1210059/20/20 6:31 AM9/20/20 7:33 AM         
1210059/20/20 1:43 AM9/24/20 4:07 PM0 1210059/20/20 10:11 AM9/24/20 11:19 AM         
1210059/30/20 7:28 AM10/6/20 9:52 AM0 1210059/30/20 7:43 AM9/30/20 9:27 AM         
1210059/30/20 7:28 AM10/6/20 9:52 AM1 12100510/1/20 11:22 AM10/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:

 

  1. A patient may be hospitalized more than once. Within each hospitalization a patient may have one or more visits to the ICU.

  2. Any hospitalization without a discharge date-time (the patient hadn’t been discharged yet) is deleted.

  3. Any ICU visit lasting less than two hours is deleted.

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

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

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

 

0 Likes
Reply
11 REPLIES 11
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

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

Posted 09-24-2020 12:46 PM (834 views)  |   In reply to DocMartin

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/

--
Paige Miller
0 Likes
Reply
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

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

Posted 09-24-2020 01:32 PM (804 views)  |   In reply to PaigeMiller

@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

0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

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

Posted 09-24-2020 01:46 PM (799 views)  |   In reply to DocMartin

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

--
Paige Miller
0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

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

Posted 09-24-2020 02:48 PM (767 views)  |   In reply to PaigeMiller

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

 

--
Paige Miller
0 Likes
Reply
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

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

Posted 09-24-2020 03:24 PM (749 views)  |   In reply to PaigeMiller
If a patient has duplicate hadmit & hdischarge times, then use the 2nd one. Thanks!
0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

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

Posted 09-24-2020 03:28 PM (745 views)  |   In reply to DocMartin

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;
--
Paige Miller
Reply
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

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

Posted 09-24-2020 04:58 PM (728 views)  |   In reply to PaigeMiller

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

0 Likes
Reply
mkeintz
PROC Star mkeintz
PROC Star

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

Posted 09-24-2020 11:41 PM (683 views)  |   In reply to DocMartin

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?

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
0 Likes
Reply
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

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

Posted 09-25-2020 10:42 AM (645 views)  |   In reply to mkeintz

@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

0 Likes
Reply
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

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

Posted 09-25-2020 10:33 AM (650 views)  |   In reply to DocMartin

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

0 Likes
Reply
DocMartin
Quartz | Level 8 DocMartin
Quartz | Level 8

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

Posted 09-25-2020 02:57 PM (628 views)  |   In reply to DocMartin

@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!

0 Likes
Reply

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • ‎09-24-2020 12:24 PM
  • 857 views
  • 1 like
  • 3 in conversation