Date:         Fri, 3 Aug 2007 11:33:16 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: One to many matching on range of dates
Comments: To: Bob_Abelson@hgsi.com
In-Reply-To:  <OF9C256F5C.6154DAF7-ON8525732C.004DF104-8525732C.00500EEB@hgsi.com>
Content-Type: text/plain; charset="us-ascii"

Bob;
I'd include a logical expression to constrain the yield of the query to
those that meet your date condition. Something along these lines
        select *
        from lotnos a full join doses b
        on a.csid=b.csid AND a.d_admin <= b.d_admin
        ;
The ON condition now specifies tuples that link on csid values and have
dispense dates (a) on or before dose dates (b).

A FULL JOIN yields the key INTERSECTION of two datasets plus the UNION
of their key set complements. So it will include all of the tuples that
fail to meet the ON criteria with NULL values for attributes in the
complements. (Think of a Venn diagram that has overlapping circles with
complements on each side.) An INNER or RIGHT JOIN might give you more
concise results. The FULL JOIN selects tuples that don't belong to the
key intersection.
S


-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Bob_Abelson@hgsi.com
Sent: Friday, August 03, 2007 10:34 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: One to many matching on range of dates


I have two datasets:

DISPENSE has subject ID (CSID),  lot number (LOTNO), and dispensing date
(D_DATE), with multiple records for each subject (one per drug kit
dispensed). TREATED has subject ID (CSID) and dosing date (D_ADMIN),
with multiple records for each subject (one dose for each date).

How can I match these up so that the dose is associated with the
appropriate lot number (the dispensing date is on or before the dosing
date).

I have tried something like this:

proc sql;
        create table lotnos as
        select distinct csid, d_date as d_admin, lotno
        from dispense
        where lotno is not null
        order by csid, d_admin;

        create table doses as
        select distinct csid, d_admin
        from treated
        order by csid, d_admin;

        create table joined as
        select *
        from lotnos a full join doses b
        on a.csid=b.csid;
quit;

But this is giving me way too many records.

Thanks.

Bob Abelson
HGSI
240 314 4400 x1374
bob_abelson@hgsi.com