Thanks Tom for the reply. but, by USUBJID AELLT will not achieve the below sorting order. I want AJOINID to assign as per USUBJID ASTDT AELLT that is why I am finding it very hard to do it. do you have any idea how to achieve this one ? (again, thanks for all your reply 🙂 )     ... View more

Yes, I have that query too, but my analysis plan doc. want to treat this as separate in AJOINID as 1st obs. (pervious one) has missing end date, which is not equal to the start date (2nd observation) I am not able to do it programmatically this 😞 someone please help me     ... View more

I would require AJOINID = 10 for 19th observation , as ASTDTC is not equal to previous AENDT     ... View more

Ok, I got this one but at one point I am stuck I am using below code to get AJOINID values.   data test; SET test; by usubjid astdt aellt; if first.aellt=1 then ajoinid+1; if first.usubjid=1 then ajoinid=1; run;   but as you see, at highlighted part, I am getting wrong values. I only want to repeat number when for same USUBJID , same AELLT if previous AENDT same as current ASTDTC   on the observation no. 19 then previous AENDT is missing and current ASTDTC is 27MAR2025, so I want to continue with AJOINID = 10 and 11 for last observation.   Please help me with this  I am attaching the code for input dataset .     data test; infile datalines dsd dlm=',' truncover; length USUBJID $20 AELLT $50 ASTDT $9 AENDT $9 ASTDTC $9; input USUBJID $ AELLT $ ASTDT $ AENDT $ AJOINID ASTDTC $; datalines; 101,Fat,17APR2025,27APR2025,1,17APR2025 101,Fat,17APR2025,.,1,27APR2025 101,Nausea,17APR2025,29APR2025,2,17APR2025 101,Anorexia,23APR2025,29APR2025,3,23APR2025 101,Arthralgia,27APR2025,14MAY2025,4,27APR2025 101,Arthralgia,27APR2025,15MAY2025,4,14MAY2025 101,Arthralgia,27APR2025,.,4,15MAY2025 101,Hemorrhoids,05MAY2025,.,5,05MAY2025 101,Vomiting,27MAY2025,27MAY2025,6,27MAY2025 201,Genital bleeding,18JAN2025,30JAN2025,1,18JAN2025 201,Nausea,23JAN2025,.,2,23JAN2025 201,Pelvic pain,23JAN2025,.,3,23JAN2025 201,Genital bleeding,08FEB2025,13FEB2025,4,08FEB2025 201,Asthenia,27FEB2025,.,5,27FEB2025 201,Vaginal dryness,27FEB2025,.,6,27FEB2025 201,Genital bleeding,01MAR2025,01MAR2025,7,. 201,Diarrhea,27MAR2025,.,8,27MAR2025 201,Rash,27MAR2025,.,9,27MAR2025 201,Rash,27MAR2025,.,9,27MAR2025 201,Hyponatremia,08MAY2025,.,10,08MAY2025 ; run; data test (drop = astdtx aendtx astdtcx ajoinid); retain usubjid aellt astdtc aendt astdt ajoinid ; set test (rename=(astdt=astdtx aendt=aendtx astdtc =astdtcx )); astdt = input(strip(astdtx),??date9.); aendt = input(strip(aendtx),??date9.); astdtc = input(strip(astdtcx),??date9.); format astdt aendt astdtc date9.; run;   ... View more

Regarding Overlapping, we need to consider Overlapping AE if pervious AENDT is equal to ASTDT   ... View more

please consider other data as input except AJOIND variable, I wanted to present (newly create) AJOINID as given then code(dataset) ... View more

Hi Kurt, apology. here I am attaching the code. could you please help me with the solution data test; infile datalines dsd dlm=','; length USUBJID $20 AELLT $50 AESTDTC $10 AEENDTC $19 ; input USUBJID $ AJOINID AELLT $ AESTDTC $ ASTDT date9. AEENDTC $ AENDT date9.; format astdt aendt date9.; datalines; 101,1,BBL,2024-12-23,23DEC24,2025-01-07,07JAN25 101,2,Hyperbilirubinemia,2025-01-14,14JAN25,2025-01-29,29JAN25 101,3,API,2025-01-22,22JAN25,2025-02-06,06FEB25 101,4,Anme,2025-01-22,22JAN25,2025-01-26,26JAN25 101,5,Hypoalbuminemia,2025-01-22,22JAN25,2025-01-30,30JAN25 101,6,Leuk,2025-01-22,22JAN25,2025-01-26,26JAN25 101,6,Leuk,2025-01-26,22JAN25,2025-01-26,26JAN25 101,7,Lymphocytopenia,2025-01-22,22JAN25,2025-01-26,26JAN25 101,8,Neut,2025-01-22,22JAN25,2025-01-22,22JAN25 101,9,Thromb,2025-01-22,22JAN25,2025-01-25,30JAN25 101,9,Thromb,2025-01-25,22JAN25,2025-01-26,30JAN25 101,9,Thromb,2025-01-26,22JAN25,2025-01-27,30JAN25 101,9,Thromb,2025-01-27,22JAN25,2025-01-29,30JAN25 101,9,Thromb,2025-01-29,22JAN25,2025-01-30,30JAN25 101,9,Thromb,2025-01-30,22JAN25,2025-01-30,30JAN25 101,10,Febrile Neut,2025-01-23,23JAN25,2025-01-24,24JAN25 101,11,Neut,2025-01-25,25JAN25,2025-01-26,26JAN25 101,12,Pancytopenia,2025-01-25,25JAN25,2025-02-03,03FEB25 101,13,Anme,2025-01-27,27JAN25,2025-01-28,28JAN25 101,14,Leuk,2025-01-27,27JAN25,2025-01-27,27JAN25 101,15,Lymphocytopenia,2025-01-27,27JAN25,2025-01-27,27JAN25 101,16,Neut,2025-01-27,27JAN25,2025-01-28,28JAN25 101,17,Leuk,2025-01-28,28JAN25,2025-02-06,06FEB25 101,18,Anme,2025-01-29,29JAN25,2025-01-29,29JAN25 101,19,Neut,2025-01-29,29JAN25,2025-02-06,06FEB25 101,20,Anme,2025-01-30,30JAN25,2025-01-30,30JAN25 101,21,Anme,2025-01-31,31JAN25,2025-03-05,05MAR25 101,22,Leuk,2025-02-07,07FEB25,2025-02-26,26FEB25 101,23,Neut,2025-02-07,07FEB25,2025-02-13,13FEB25 101,24,Neut,2025-02-14,14FEB25,2025-02-27,27FEB25 101,25,Arthralgia,2025-02-18,18FEB25,2025-05-10,10MAY25 101,26,Asthenia,2025-02-18,18FEB25,2025-05-10,10MAY25 101,27,API,2025-02-28,28FEB25,2025-03-13,13MAR25 101,28,Leuk,2025-02-28,28FEB25,2025-03-05,05MAR25 101,29,Neut,2025-02-28,28FEB25,2025-03-05,05MAR25 101,30,Thromb,2025-02-28,28FEB25,2025-03-13,13MAR25 101,31,Anme,2025-03-06,06MAR25,2025-03-13,13MAR25 101,32,Leuk,2025-03-06,06MAR25,2025-05-15,15MAY25 101,33,Neut,2025-03-06,06MAR25,2025-03-13,13MAR25 101,34,Anme,2025-03-14,14MAR25,2025-03-27,27MAR25 101,35,Neut,2025-03-14,14MAR25,2025-04-25,25APR25 101,36,API,2025-03-28,28MAR25,2025-04-10,10APR25 101,37,Anme,2025-03-28,28MAR25,2025-04-10,10APR25 101,38,Thromb,2025-03-28,28MAR25,2025-04-10,10APR25 101,39,Anme,2025-04-11,11APR25,2025-04-25,25APR25 101,40,Anme,2025-04-26,26APR25,2025-05-15,15MAY25 101,41,Neut,2025-04-26,26APR25,2025-05-15,15MAY25 101,42,Thromb,2025-04-26,26APR25,2025-05-08,08MAY25 101,43,Anme,2025-05-16,16MAY25, ,. 101,44,Leuk,2025-05-16,16MAY25, ,. 102,1,Vom,2025-03-26,26MAR25,2025-04-15,15APR25 102,2,Vomiting,2025-04-01,01APR25,2025-04-01,01APR25 102,3,Vom,2025-04-02,02APR25,2025-04-04,04APR25 102,4,Asthenia,2025-04-10,10APR25, ,. ; run; ... View more

Hi Kurt if possible, could you please provide solution for this task ... View more

Hi Kurt  actually I wanted to present AESTDTC in ISO8601 format in my dataset.  sorry for any confusion.       ... View more

ohhh, no. this code is not working . I think it requires proper sorting . if the datalines as in the below code (I just shuffle few lines as compare to previous code).  this solution is not working.   data test; infile datalines dlm='09'x dsd truncover; informat aendt date9.; input usubjid :$3. aestdtc :$10. aendt :date9. ae :$200.; format aendt date9.; datalines; 101 2024-12-19 26DEC24 Constipation 101 2024-12-30 10FEB25 Thrombopenia 101 2025-01-27 07FEB25 Neutropenia 101 2025-01-13 23MAR25 Anemia 101 2025-01-25 10FEB25 Dizziness 101 2025-01-25 10FEB25 Dyspnea 101 2024-12-18 28JAN25 Appetite lost 101 2025-01-25 . Fatigue 101 2025-02-10 23MAR25 Anemia 101 2025-01-27 31JAN25 Nausea 101 2025-01-27 31JAN25 WBC decreased 101 2025-02-03 15FEB25 Rhinitis 101 2025-03-01 . Epigastralgia 101 2025-03-01 03MAR25 Nausea 101 2025-03-01 03MAR25 Vomiting 101 2025-01-06 07FEB25 Neutropenia 101 2025-03-24 . Anemia 101 2025-03-24 . Thrombopenia ; run;   ... View more

Thanks Mkeintz,   with this code the JOIN values are fine but not ASTDT   for eg. for observation no. 5 I want ASTDT as 06JAN2025 as this is overlapping AE     could you please help me with this ?   ... View more

still it is not working .... i am getting below result in this case     ... View more

This will not work as I require result per USUBJID and I want AESTDTC as character in final dataset (as in input itself) ... View more