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

Thanks to all who replied (Ya, Sig, and Mark). I have decided to use
Mark's solution, in its final form.

proc sql;
    create table spans(drop=diff) as
    select a.csid,
           coalesce(a.d_admin,'01JAN1900'd) as start_date format=date9.,
           coalesce(b.d_admin,'12DEC2999'd)-1 as end_date format=date9.,
           a.lotno,
           intck('day',calculated start_date,calculated end_date) as
diff
    from
           lotnos as a
        full join
           lotnos as b
        on
           a.csid eq b.csid
        and
           a.d_admin ne b.d_admin
        and
           a.d_admin lt b.d_admin
    group by
           a.csid,
           calculated start_date
    having
           a.csid ne .
       and
           min(diff) eq diff
    ;

    create table joined as
    select
             a.csid,
             b.start_date as d_dispense,
             a.d_admin,
             b.lotno
    from
             doses as a
        left join
             spans as b
        on
             a.csid=b.csid
        and
             a.d_admin between b.start_date and b.end_date
    order by
             csid,
             d_admin,
             d_dispense
    ;
quit;



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



Bob Abelson/Hgsi
08/03/2007 10:34 AM

To
SAS-L@LISTSERV.UGA.EDU
cc

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