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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.