AE overlapping and duplicate entries

[nandini]

Hi all,

I need to do a listing on this.

Exception list:  Comparison of overlapping events (AE#1 term = AE#2 term) when  AE # <1> start date <=  AE # <2> start date <= AE #<1> end date.

I need help figuring it what does the above exactly mean ? how can i proceed ? what logic should i apply ?

Thanks,

Nandini.

[Jagadishkatam]

Hi Nandini,

Please refer to the link (sas programming in pharmaceutical industry.pdf)

http://www.planta.cn/forum/files_planta/sas_programming_in_the_pharmaceutical_industry_119.pdf

please refer page 106, topics Performing Many-to-Many Comparisons/Joins and Program 4.8 Performing a Many-to-Many Join with PROC SQL.

This will provide you with the required information pertaining to your question.

Thanks,

Jagadish

[Jagadishkatam]

Could you please let me know the difference between the ae#1 term and ae#2 term? This will help me to provide a helpful suggestion.

[nandini]

Hi Jagadish,

I understand the coding part but not very sure if i am on right track . I am assuming to flag if the records have the second adverse event falling in between first adverse event start date and stop date !!

So could you please explain me the functionality and how to apply ? so that what exactly is required or to be considered like would it be enough if i consider just the start and stop dates or should i have to play with any other variables to check for the overlapping events.

Sure, in my data it is given as adverse event 1 (ae#1 term) as pain and adverse event 2(ae #2 term)  as rash and so on ...

Thanks in advance !!

Nandini.

[nandini]

libname datapath "&source" inencoding=any;

data ae;

  set datapath.ae;

run;

proc sort data = ae out = ae1 noduprecs;

  by usubjid aestdtc aeendtc;

run;

data ae2;

  set ae1;

  length aeendtc1 $20.;

  by usubjid;

  retain aeendtc1 ;

  if first.usubjid then aeendtc1 = .;

  output;

  aeendtc1 = aeendtc;

run;

data ae3;

  set ae2;

format ae_std  ae_end ae_endtc1 yymmdd10.;

ae_std = input(aestdtc,yymmdd10.);

ae_end = input (aeendtc,yymmdd10.);

ae_endtc1 =input(aeendtc1,yymmdd10.);

run;

data  ae4;

  set ae3;

  by usubjid;

  if not first.usubjid then do ;

/* if (not first.usubjid) and (aedecod1 = aedecod2)then do ;*/

  if ae_std <= ae_endtc1 then flag = 'yes';

  else flag = 'no';

  end;

run;

proc sort

  data = ae4

  out  = final;

  by usubjid aestdtc aeendtc aeendtc1 flag ;

run;

title2 "&reporttitle";

proc report data = final nowd;

  columns usubjid aestdtc aeendtc aeendtc1 flag ;

  define usubjid / display ;

  define aestdtc / "AE Start Date" center;

  define aeendtc / "AE End Date" center;

  define flag / "flag";

run;

This is how i programmed !!

[Jagadishkatam]

Hi Nandini,

Please find below the code which i worked on a sample data, this is as per your requirement.

except the first record, remaining records will be flagged accordingly. i have compared the ae start and even the ae end of the next records with the ae start and ae end of the first records. Because if the ae end date of the next record is between the ae start and ae end of the first records, it means that the ae was overlapping with the first record. i believe this was not considered in your code above.

Please try and let me know if this is what you were expecting

data aes;

informat ae_start date9. ae_stop date9.;

input @1 subject $3.

@5 ae_start date9.

@15 ae_stop date9.

@25 adverse_event $15.;

format     ae_start  ae_stop date9.;

datalines;

101 01JAN2004 02JAN2004 Headache

101 02JAN2004 03FEB2004 Back Pain

102 03NOV2003 10DEC2003 Rash

102 10NOV2003 10JAN2004 Abdominal Pain

102 04APR2004 04APR2004 Constipation

;

run;

proc sort data=aes;

    by     subject  ae_start  ae_stop;

run;

data want;

    set aes;

    by     subject  ae_start  ae_stop;

    retain old_aest old_aeend;

    if first.subject then do;

    old_aest=ae_start;

    old_aeend= ae_stop;

    end;

    if not first.subject then do;

    if     (old_aest<=ae_start <= old_aeend) or (old_aest<=ae_stop <= old_aeend) then flag='Yes';

    else flag='No';

    end;

    format     old_aest old_aeend date9.;

run;

proc print;

run;

Thanks,

Jagadish

[nandini]

Hi Jagadish,

Thank you so much for the sample data and explanation. I understood the point of overlapping .

Here comes the case for which the purpose is to identify overlapping and duplicate entries for the AE data.

Comparison of overlapping events (AE#1 term = AE#2 term) when  AE # <1> start date <=  AE # <2> start date <= AE #<1> end date. What i am assuming is to flag or to list those records having overlapping events and small correction ae#1 term and ae#term suppose if they are same events , then also is the case overlapping events ?

Previously  i did  flagging only those records with overlapping dates but where as the task required is comparison of overlapping events. So now how can i proceed with this ?

Besides, i am also not able to understand what logic can i use for Comparison of overlapping events (AE#1 term = AE#2 term) when  AE # <1> start date <=  AE # <2> start date <= AE #<1> end date. Also please let me know what can i do for if the dates are missing . Like for some ending dates are missing where just the start dates are there !!

Example : suppose

101 01JAN2004 02JAN2004 Headache

101 02JAN2004 03FEB2004 Back Pain

102 03NOV2003 10DEC2003 Rash

102 10NOV2003 10JAN2004 Abdominal Pain

102 04APR2004 06APR2004 Constipation

102 05 july2004 07 july2004  Back pain

102 05APR2004 07APR2004 Constipation

102 04APR2004 04APR2004 Headache

Thanks in Advance !!

Nandini.

[Jagadishkatam]

Here i would like to check with you, like we need to compare the ae terms including the dates of second record with previous record or its comparison of first record with next all records. could you please provide a clarification on this.

i believe the best thing to avoid any confusion is to show the sample output you are expecting with the flags. This will help me to provide the suggestion.

Thanks,

Jagadish

[nandini]

Sorry Jagadish for the confusion !!!

Yes,we need to compare the ae terms including the dates of comparison of first record with next all records for each subject.

Assuming that this is the data:

101 01JAN2004 02JAN2004 Headache

101 02JAN2004 03FEB2004 Back Pain

102 03NOV2003 10DEC2003 Rash

102 10NOV2003 10JAN2004 Abdominal Pain

102 04APR2004 06APR2004 Constipation

102 05 july2004 07 july2004  Back pain

102 05APR2004 07APR2004 Constipation

102 04APR2004 04APR2004 Headache

Sample output is :

102 04APR2004 06APR2004 Constipation  y

102 05APR2004 07APR2004 Constipation  y

Please let me know if i am still unclear .

Thanks,

Nandini.

[Jagadishkatam · Accepted solution]

Hi nandini,

this is based on the comparing the currents records from previous records by ae terms and dates, so there are two flags one for dates and one of ae terms.

As per the output you have given where you mentioned that we need to compare the first record with all other records, does not meet the output you displayed. This is because for subject 102 the first ae record is rash and next none of the records have rash. however in the output you have flagged constipation which is not the first record.

as per my understanding you are trying to compare the current record with previous record, then constipation will be flagged. Please try the below code and check an let me know if you getting the desired output.

let me know if it does not meet you expectation.

data aes;

informat ae_start date9. ae_stop date9.;

input @1 subject $3.

@5 ae_start date9.

@15 ae_stop date9.

@25 adverse_event $15.;

format ae_start  ae_stop date9.;

datalines;

101 01JAN2004 02JAN2004 Headache

101 02JAN2004 03FEB2004 Back Pain

102 03NOV2003 10DEC2003 Rash

102 10NOV2003 10JAN2004 Abdominal Pain

102 04APR2004 06APR2004 Constipation

102 05jul2004 07jul2004 Back pain

102 05APR2004 07APR2004 Constipation

102 04APR2004 04APR2004 Headache

;

run;

proc sort data=aes;

    by subject  ae_start  ae_stop;

run;

data want;

    set aes;

    by subject  ae_start  ae_stop;

    old_aest=lag(ae_start);

    old_aeend=lag(ae_stop);

    first_ae=lag(adverse_event);

    if first.subject then do;

    old_aest=.;

    old_aeend=.;

    first_ae='';

    end;

    if not first.subject then do;

    if (old_aest<=ae_start <= old_aeend) or (old_aest<=ae_stop <= old_aeend) then flag='Yes';

    else flag='No';

    if first_ae=adverse_event then ae_flag='Yes';

    else ae_flag='No';

    end;

    format old_aest old_aeend date9.;

run;

proc print;

run;

Thanks,

jagadish

[nandini]

Hi Jagadish,

Thanks a ton !! I really require the output for what you have coded !!

I somehow felt I am comparing the currents records from previous records by ae terms and dates but then later I realized that for the output I displayed is more towards the other way.

Anyways thank you once again !!

If you don't mind could you please give me your email address !! As if I am not able to reach you through SAS Community, I can email you my doubts.

Thanks,

Nandini.

[Jagadishkatam]

Hi Nandini,

Thanks for your message, i sent you my email address by direct message to you.

regards,

jagadish

[nandini]

Thanks Jagadish and I have sent you an email.

Thanks,

Nandini.

[nandini]

Thanks in Advance !!

Regards,

Nandini.