Merging data sets with more than one condition using proc sql

[knveraraju91]

Dear,

I am merging ae and ex data sets  to get last dose on or before AE event. I need help in my SQL code to get the output. Please help . Thank you.

data ae;
input id term$ aedate $10.;
datalines;
1 ache 2015-10-05
1 ache 2015-10-08
2 pain 2015-11-10
2 ache 2015-11-07
3 ache 2015-10
4 ache 2015-11
5 pain 2015-12-07
;
data ex;
input id dose exdate $10.;
datalines;
1 10 2015-10-04
1 20 2015-10-06
1 30 2015-10-08
2 15 2015-11-05
2 30 2015-11-05
2 25 2015-11-09
3 40 2015-10-05
3 50 2015-10-09
3 60 2015-10-15
4 22 2015-10-29
4 26 2015-11-09
4 29 2015-11-19
5 50 2015-12-09
5 50 2015-12-15
5 50 2015-12-20
;

data ae1;
set ae;
if length(aedate) lt 10 then do;
aedate=strip(aedate)||'-'||'01';
adtf='D';
end;
run;

proc sql;
create table one as
select a.*,b.dose as DOAEON 
 from ae1 as a left join ex as b 
  on a.id=b.id and a.Aedate-b.exdate >=0
    group by a.id,a.aedate 
     having a.Aedate-b.exdate=min(a.Aedate-b.exdate);
quit;

output needed;

id   term      aedate        dose
1   ache    2015-10-05      10             /* Dose taken on or before AE event.*/        
1   ache    2015-10-08      30              /*Dose taken on or before AE event.*/
2    pain     2015-11-10     25              /*Dose taken on or before AE event.*/
2    ache     2015-11-07    15               /*if subject has taken two doses on same day, consider small dose*/
3    ache     2015-10-01    40      / partial date(adtf='D') and no dose value present prior to aedate then first exdose consider*/
4    ache      2015-11-01    22    / partial date(adtf='D') and dose value present prior to aedate then on or before dose consider*/
5     pain      2015-12-07     .     /*complete date with no prior dose taken before aedate, then no dose populated*/

[Ksharp]

data ae;
input id term$ aedate $10.;
datalines;
1 ache 2015-10-05
1 ache 2015-10-08
2 pain 2015-11-10
2 ache 2015-11-07
3 ache 2015-10
4 ache 2015-11
5 pain 2015-12-07
;
data ex;
input id dose exdate : yymmdd10.;
format exdate yymmdd10.;
datalines;
1 10 2015-10-04
1 20 2015-10-06
1 30 2015-10-08
2 15 2015-11-05
2 30 2015-11-05
2 25 2015-11-09
3 40 2015-10-05
3 50 2015-10-09
3 60 2015-10-15
4 22 2015-10-29
4 26 2015-11-09
4 29 2015-11-19
5 50 2015-12-09
5 50 2015-12-15
5 50 2015-12-20
;
proc sort data=ex;
 by id exdate dose;
run;
data new_ex;
 set ex;
 by id exdate ;
 if first.exdate;
 rename exdate=date;
run;

data ae;
 set ae;
 date=input(aedate,?? yymmdd10.);
 if missing(date) then do;date=input(cats(aedate,'-01'), yymmdd10.);flag=1;end;
 format date yymmdd10.;
run;
proc sort data=ae;
 by id date;
run;
data temp;
 set new_ex ae(in=inb);
 by id date;
 retain _dose;
 if first.id then call missing(_dose);
 if not missing(dose) then _dose=dose;
 want=inb;
 drop dose;
run;
proc sort data=temp;
 by id descending date;
run;
data want;
 set temp;
 by id;
 retain dose;
 if first.id then call missing(dose);
 if not missing(_dose) then dose=_dose;
 if flag and missing(_dose) then _dose=dose;
 if want;
 keep id date term _dose;
run;
proc sort data=want;
 by id date;
run;

[Jagadishkatam]

Please try the sql approach

data ae1;
set ae;
if length(aedate) lt 10 then do;
aedate=strip(aedate)||'-'||'01';
adtf='D';
end;
aedt=input(aedate,yymmdd10.);
format aedt date9.;
row=_n_;
run;

proc sql;
create table want as select a.*,b.dose,b.exdt from ae1 as a left join ex as b on a.id=b.id and b.exdt<=a.aedt order by a.id,a.term,a.aedt,b.exdt;
create table want_ as select a.id,a.term,a.aedt,b.dose as dose2,b.exdt as exdt2 from ae1(where=(adtf='D')) as a left join ex as b on a.id=b.id order by a.id,a.term,a.aedt,b.exdt;
quit;

data want_;
set want_;
by id term aedt;
if first.aedt;
run;

data want2;
set want;
by id term aedt;
if last.aedt;
run;

data want3;
merge want2 want_;
by id term aedt;
if dose eq . then dose=dose2;
keep id term aedate dose;
run;