Date:         Tue, 6 Nov 2007 14:09:21 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Help on data look up between two data set

On Fri, 2 Nov 2007 12:44:01 -0400, Sophia Tong <sophiDT@HOTMAIL.COM> wrote:

>Dear list,
>
>I am trying to figure out when a patient come to ER, how many other patients
>are seen by nurse, or in waiting room or seen by doctors. There is no key
>between two tables I have, so it's a many-to-many comparison.  The data I
>have look like the following:
>
>table A:
>patient_id arrive_time
>1         9:25:00
>2         13:32:00
>
>table B:
>nurse_start nurse_end room_start room_end
>8:45:00    8:55:00  9:00:00          9:15:00
>8:47:00         8:52:00         8:58:00          9:22:00
>8:58:00         9:12:00         9:16:00          9:23:00
>9:11:00         9:19:00         9:24:00          9:45:00
>10:14:00 10:21:00 10:25:00 10:50:00
>10:18:00 10:26:00 10:30:00 10:55:00
>10:33:00 10:41:00 10:45:00 10:59:00
>11:31:00 11:39:00 11:42:00 12:15:00
>11:45:00 11:52:00 11:55:00 12:35:00
>13:12:00 13:17:00 13:23:00 13:55:00
>
>output want:
> If arrive time fall into nurse time, then make nurse_flg=1. If it fall into
>room waiting time, then room_flg=1.
>
>My real world is that there are 1000 patients in table A and 20,000 obs in
>table B. Tried SQL, somehow didn't work.
>
>Please offer your expertise.
>
>Thanks in advance.
>
>Sophia

In the real data set, are the 5 time variables actually datetimes?

If not, and assuming that this ER stays open overnight, you would have
intervals which begin before midnight and end after midnight. It is easy to
process these incorrectly and easy to overlook the consequences.