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*/
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.