I guys,
I would like to only keep the VISIT record 14JAN2009 as record imputed to the Adverse Event (START: 07JAN2009, STOP: 07JAN2009) because is the record after the STOP of this Adverse Event , but i am not interested in keep any more days, i.e VISIT day 22FEB2009...because only I am interested in the record after the STOP date of the Adverse Event.
Any help?
Thanks.
Please find the code below:
informat START date9. STOP date9.;
input SUBJID START STOP;
format START STOP date9.;
cards;
1 7JAN2009 07JAN2009
data ASSIGNED;
informat VISIT date9.;
input SUBJID VISIT;
format VISIT date9.;
cards;
1 02APR2008
1 07APR2008
1 12MAY2008
1 30JUN2008
1 01DEC2008
1 22FEB2009
1 14JAN2009
2 08DEC2008
2 22DEC2008
2 30JAN2009
*I start assigned the possibles VISITS to to this Adverse Event;
PROC SQL;
CREATE table temp AS
SELECT a.SUBJID, ae.START, ae.STOP, a.visit , case when a.VISIT BETWEEN ae.START AND ae.STOP then 1 else 0 end as flag
FROM ae LEFT join ASSIGNED a ON ae.SUBJID=a.SUBJID
ORDER BY a.SUBJID,a.visit
;
QUIT;
*I start selecting the only visits after the Stop of adverse events...now I get stuck after this point...;
*Thanks in advance;
data tempFILTER;
set temp;
LAGSTOP=LAG(STOP);
LAGVISIT=LAG(VISIT);
if (flag=1) or (subjid=lag(subjid) and START=STOP and VISIT GT STOP ) then output=1;
else output=0;
run;
why not just apply the rules you have described in a simple PROC SQL query
having the first visit after the adverse event "stop"
proc sql _method ;
create table wanted as
select e.*, s.visit
from ae e
join assigned s
on e.SUBJID = s.SUBJID
and s.visit > e.stop
having (s.visit - e.stop) = min(s.visit - e.stop)
;
quit ;
proc sql under the covers will do the sorting and merging for you, so it will probably take just as long as the multiple steps to avoid SQL. However this kind of sql query allows your syntax to appear a lot more like the original question
peterC
Hi,
you can pull only that record from the adverse event dataset as that is the only visit where the subject got the adverse event.
For this first you need to create a vistdt(visit start date) and visdt(visit end date). Because you have in adverse event (ae) dataset both the start date and stop date.
Consider that the date in the assigned dataset is visit end date, derive the visit start date. Once you derive the visit start date, you will have both the dates(visit start and end) as those available in AE dataset.
Use proc sql to create the running records, where you can map the adverse event dates with the visit dates, to check from which visit to which visit the adverse event existed.
Please follow the below code,
data AE;
informat START date9. STOP date9.;
input SUBJID START STOP;
format START STOP date9.;
cards;
1 7JAN2009 07JAN2009
run;
data ASSIGNED;
informat VISIT date9.;
input SUBJID VISIT;
format VISIT date9.;
cards;
1 02APR2008
1 07APR2008
1 12MAY2008
1 30JUN2008
1 20OCT2008
1 01DEC2008
1 22FEB2009
1 14JAN2009
2 27NOV2008
2 08DEC2008
2 22DEC2008
2 30JAN2009
*I start assigned the possibles VISITS to this Adverse Event;
run;
/*sort the assigned dataset on subjid and visit(dates)*/
proc sort data=assigned;
by subjid visit;
run;
/*to create the visid based on the visit(dates), this is necessary to derive the visit start and end dates*/
data assigned2;
set assigned;
retain visid;
by subjid;
if first.subjid then visid=1;
else visid+1;
run;
/*sort by subjid and visid*/
proc sort data=assigned2;
by subjid visid;
run;
/*to create the visit start date*/
data assigned3;
set assigned2;
by subjid visid;
if first.visid then visdt=visit;
vistdt=lag(visit);
format visdt vistdt date9.;
if first.subjid then vistdt=visdt;
drop visit;
run;
/*to map the adverse event with the visit dates*/
proc sql;
create table run as
select a.*,b.visid,b.visdt,b.vistdt from ae as a left join assigned3 as b on a.subjid=b.subjid
and ((a.start<=b.vistdt<=a.stop) or
(a.start<=b.visdt<=a.stop) or
((b.vistdt<a.start) and (a.stop < b.visdt)));
quit;
/*end*/
Now you will get only one record at visid 7, as the adverse event start and stop date is 7JAN2009 which falls between the visit start (01DEC2008) and visit end date(14JAN2009).
Hope this helps you.
Thanks,
Jagadish
Thank you Jagadishkatam!
why not just apply the rules you have described in a simple PROC SQL query
having the first visit after the adverse event "stop"
proc sql _method ;
create table wanted as
select e.*, s.visit
from ae e
join assigned s
on e.SUBJID = s.SUBJID
and s.visit > e.stop
having (s.visit - e.stop) = min(s.visit - e.stop)
;
quit ;
proc sql under the covers will do the sorting and merging for you, so it will probably take just as long as the multiple steps to avoid SQL. However this kind of sql query allows your syntax to appear a lot more like the original question
peterC
Brilliant, Thanks!
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.