标题: few to many merge? [打印本页] 作者: shiyiming 时间: 2012-3-21 01:35 标题: few to many merge? I have two datasets need to merge.
The first one is a big dataset with[b:2iuotagz] studyid[/b:2iuotagz] and[b:2iuotagz] discharg[/b:2iuotagz](the date when the patients got their discharge).
The second one is the fewer observations than first one. They have two columns: [b:2iuotagz]studyid[/b:2iuotagz] and [b:2iuotagz]call_mad[/b:2iuotagz](the date when [u:2iuotagz][i:2iuotagz]nurse call the patient after discharge date[/i:2iuotagz][/u:2iuotagz]). Not all discharges get a call from nurse.
[u:2iuotagz][b:2iuotagz]The first table is
[/b:2iuotagz][/u:2iuotagz]
STUDYID DISCHARG
10011 2008-10-29
10011 2008-11-7
10011 2008-11-18
10011 2009-10-17
10011 2010-1-2
10011 2010-1-22
[u:2iuotagz][b:2iuotagz]The second table is
[/b:2iuotagz][/u:2iuotagz]
STUDYID CALL_MAD
10011 2009-10-19
10011 2010-1-25
[u:2iuotagz][b:2iuotagz]The final table I want[/b:2iuotagz][/u:2iuotagz]
STUDYID DISCHARG CALL_MAD
10011 2008-10-29
10011 2008-11-7
10011 2008-11-18
10011 2009-10-17 2009-10-19
10011 2010-1-2
10011 2010-1-22 2010-1-25
Hopefully, it is clear. Thanks in advance.
Jane作者: shiyiming 时间: 2012-3-28 04:03 标题: Re: few to many merge? [code:vjhnikr4]data a;
input studyid discharg yymmdd10.;
pk=_n_;
cards;
10011 2008-10-29
10011 2008-11-7
10011 2008-11-18
10011 2009-10-17
10011 2010-1-2
10011 2010-1-22
;
data b;
input studyid call_mad yymmdd10.;
cards;
10011 2009-10-19
10011 2010-1-25
;
run;
proc sql;
create table c as
select d2.studyid, d2.discharg format=yymmdd10.,d1.call_mad format=yymmdd10.
from (select *,min(abs(call_mad-discharg)) as diff
from (select d1.*,call_mad
from a d1 left join b d2
on d1.studyid=d2.studyid)
group by call_mad
having diff>0 and call_mad-discharg=diff) d1 right join a d2
on d1.discharg=d1.discharg and d1.pk=d2.pk;
quit;[/code:vjhnikr4]