|
Hi again,
It seems like having both dates would be more useful.
Here is an additional tweak:
proc sql;
create table joined2 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
;
quit;
Mark
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Terjeson, Mark
Sent: Friday, August 03, 2007 8:08 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: One to many matching on range of dates
Hi Bob,
Here is one of several approaches:
data dispense;
csid=1; d_date='17JUN2006'd; lotno=100; output;
csid=1; d_date='15NOV2006'd; lotno=200; output;
csid=2; d_date='20AUG2006'd; lotno=300; output;
csid=2; d_date='06SEP2006'd; lotno=400; output;
format d_date date9.;
run;
data treated;
csid=1; d_admin='25APR2006'd; output;
csid=1; d_admin='18JUN2006'd; output;
csid=1; d_admin='20JUN2006'd; output;
csid=1; d_admin='22JUN2006'd; output;
csid=1; d_admin='04DEC2006'd; output;
csid=2; d_admin='22AUG2006'd; output;
csid=2; d_admin='25AUG2006'd; output;
csid=2; d_admin='11SEP2006'd; output;
format d_admin date9.;
run;
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 spans as
select a.csid,
coalesce(a.d_admin,'01JAN1900'd) as start_date format=date9.,
coalesce(b.d_admin,'12DEC2999'd) as end_date format=date9.,
a.lotno
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
having
a.csid ne .
;
create table joined as
select
a.*,
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
;
quit;
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investments
Russell Investments
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Bob_Abelson@HGSI.COM
Sent: Friday, August 03, 2007 7: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
| 
