BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michtka
Fluorite | Level 6

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:

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 to this Adverse Event;

run;

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12

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

View solution in original post

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
michtka
Fluorite | Level 6

Thank you Jagadishkatam!

Peter_C
Rhodochrosite | Level 12

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

michtka
Fluorite | Level 6

Brilliant, Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1298 views
  • 3 likes
  • 3 in conversation