Sir thanks for your reply.The rules are bit complex. As you mentioned below yesterday  -  You described a lot, but the output agrees with all except lot3 where the code carries over two drugs and their end dates while you say it shouldn't. Like I've said, I don't understand your overlapping rule(s). In previous posts, you've said that a core drug should remain active until it's end date. However, you are now saying (as I understand it) that there isn't any such applicable rule. A previous core drug is only considered if it is again prescribed during the initial 21 day period.   If that is correct, then there is no need to consider overlaps as the drug will simply be added during the initial 21 day period when it reappears. --------- This is correct.I explained you wrongly the overlapping rule in my previous post.For patient id 245689  ,output is fine as it should be.   Only thing I am not getting the desired output  for patient id 874512399  .It will be really helpful if  you kindly look into this once.   For patient id 874512399   -   Lot 1 is fine .   But for lot 2 as it is a core plus non core lot,end date would be end of latest use of core drug.While comparing 60 days criteria we should look for only core drug as it is core plus non core lot.But in our code it is now checking the maximum end date what ever available irrespective of core or non core lot .That is why we are ending the lot on 7april2016.As per the logic we should end it on 16 april2015 which is the maximum end of used core drug of lot 2 and then we check should check for a blank gapof 60 days or a new core drug to start for begining of lot 3 .As there is a blank days of 60 days after 16april2015 till 16jun2015,lot3 begins on 16thjune with a non core Bevacizumab and checking in 21 days we did not find any other drug .As only non core drug present in lot 3 ,it is non core only lot.So while checking 60 days we need to look for non core drug fom initial 21 days.After checking we found lot 3 ends on 8 th april as new core drug starts.   We are not considering whether a lot core lot or non core only lot in our code that is why we are getting 3 lot as output.But actually it should be 4.I tried to change in the code code but did not able to do that.We just need to check whther a lot core lot or non core only lot and end date should update accordingly.If it is core onlyor core plus non core lot theenddae would maximum end date of core drug after applying 21 days and 60 days rule.If a lot is non core only then end date would be the maximun end date of non core drug in initial 21 days.    I left with only this patient id .If you please look into these to impletent this logic.I would be really really grateful. Thanks in advance.   Output am currently getting -    874512399 25APR2014 21JAN2015 Dabrafenib+Trametinib_Dimethyl_Sulfoxide 1 874512399 16APR2015 07APR2016 Trametinib_Dimethyl_Sulfoxide+Bevacizumab 2 874512399 08APR2016 08APR2016 Pembrolizumab+Bevacizumab 3   Output i want to have -    patient_id         drug                                                                start_date         end_date           lot 874512399      Dabrafenib+Trametinib Dimethyl Sulfoxide   25APR2014        21JAN2015       1 874512399        Bevacizumab+Trametinib Dimethyl Sulfoxide       16APR2015        16APR2015          2 874512399        Bevacizumab                                                         16JUN2015         07APR2016         3 874512399        Pembrolizumab +Bevacizumab                             08APR2016         08APR2016         4   Data I have -    data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 874512399 Dabrafenib Core 25-Apr-14 24-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 29-Apr-14 28-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 30-May-14 28-Jun-14 874512399 Dabrafenib Core 2-Jun-14 1-Jul-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 4-Aug-14 2-Sep-14 874512399 Dabrafenib Core 5-Aug-14 3-Sep-14 874512399 Dabrafenib Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 11-Nov-14 11-Nov-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 1-Dec-14 1-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Dec-14 21-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 22-Dec-14 22-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Jan-15 21-Jan-15 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Apr-15 16-Apr-15 874512399 Bevacizumab Non_core 1-May-15 1-May-15 874512399 Bevacizumab Non_core 18-May-15 18-May-15 874512399 Bevacizumab Non_core 2-Jun-15 2-Jun-15 874512399 Bevacizumab Non_core 16-Jun-15 16-Jun-15 874512399 Bevacizumab Non_core 8-Jul-15 8-Jul-15 874512399 Bevacizumab Non_core 22-Jul-15 22-Jul-15 874512399 Bevacizumab Non_core 5-Aug-15 5-Aug-15 874512399 Bevacizumab Non_core 25-Aug-15 25-Aug-15 874512399 Bevacizumab Non_core 17-Sep-15 17-Sep-15 874512399 Bevacizumab Non_core 1-Oct-15 1-Oct-15 874512399 Bevacizumab Non_core 14-Oct-15 14-Oct-15 874512399 Bevacizumab Non_core 27-Oct-15 27-Oct-15 874512399 Bevacizumab Non_core 13-Nov-15 13-Nov-15 874512399 Bevacizumab Non_core 4-Dec-15 4-Dec-15 874512399 Bevacizumab Non_core 24-Dec-15 24-Dec-15 874512399 Bevacizumab Non_core 15-Jan-16 15-Jan-16 874512399 Bevacizumab Non_core 5-Feb-16 5-Feb-16 874512399 Bevacizumab Non_core 26-Feb-16 26-Feb-16 874512399 Bevacizumab Non_core 18-Mar-16 18-Mar-16 874512399 Bevacizumab Non_core 8-Apr-16 8-Apr-16 874512399 Pembrolizumab Core 8-Apr-16 8-Apr-16 ;     ... View more

  As I just explained above for those two patient id where am not getting the desired output .I have now explained discrepancies and overlapping  rules in details.For patient id 874512399  i ran the code you posted yesterday where you restricted the core requirement to check my desired output and that output I posted here.Let me know if you need more information.   The discrepancies are as bellows  (in bullet list)   We are missing out something while checking the overlapping rule .   The rule says  - We will not include non core drug from previous lot in current lot if it continues after start of current lot  irrespective of whether it is found anytime in 21 days or 60 days .We will include core drug from  previous lot in current lot only  if it starts again in current lot within 21 days or 60 days .   Note - If a core drug from previous lot continues on or after start of current lot and again it will start any time while checking 21 days or 60 days then the core drug or drugs from previous lot will include in current lot and end will update accordingly of the latest .E.G - Let’s assume a situation where Lot 1 ends on 1 apr 2016 .Lot 1 contains core drug C1 and non core drug N2.Both the drugs continues till 10 apr 2016.As a new core drug C3 starts on 5 apr 2015,lot 1 ends on 1 day prior to this ..Lot 1 ends on 4apr 2015..Lot2 starts on 5 th apr 2015 while checking 21 days and 60 days criteria .We did not get any other drug.So lot will have only C2.Although from lot1 core drug C1 continues till 10 apr 2015 which is a date after start of lot 2,we will not include in C1 the lot 3 as C1 did not start again any time while checking 21 days or 60 days criteria. If we had found another start of C1 anytime while checking 21 days or 60 days criteria, it would have been included in the lot .   This is the case with id 245689 with lot 3 that is why the end date of lot 3 increases to 24 july .But this is should not be the case as none of the drugs either Dabrafenib or Trametinib Dimethyl Sulfoxide starts again anytime in lot 3 .But currently the code is checking whther any of the core drug from previous lot continues or not .But it is not checking whether it starts again or not.   If a lot having both core and non core drug then end date of the lot will be the maximum end date of latest use core drug not the non core drug.Fot this if we find any core and non core drug in initial 21 days .we will check whether the core drug is again available in the 60 days checking or not. If not available then end will be the end date of latest use core drug in initial 21 days.If a lot having only non core drugs in intial 21 daysthen we will check whether any non core drug from preious lot is there in 60 days checking or not.If available the the end would the latest use non core drug.This is the case with id 874512399.   If a lot having both core and non core drug in initial 21 days.We should include both core and non drug.Currently it is not inluding non core drug in the lot .This is case with patient id 54871    I have given explanation in detail for each patient id where we are missing the rules.   For patient id 245689 - A core drug Dabrafenib starts on 14 apr 2015 and we need to check what are the other drugs available within 21 days of 14 apr 2015.We do not have any other drug in 21days.So lot 1 will have Dabrafenib.The initial end date would be 14may 2015.But as a new core drug other than lot 1 starts before 14may 2015 , lot 1 ends on 1 day prior to start of Trametinib Dimethyl Sulfoxide whiich is 5may2015.Lot 2 starts on 6 th may. Now we need to check what are other drug or drugs which starts in 21 days of 6 th may.We get did not any drug in 21 days so initial lot 2 will have Trametinib Dimethyl Sulfoxide and intial end date will be 4june2015..Now we need to check 60 days from 4june2015.We get drug dabrafenib starts on 2jun2015 .It is now a overlapping situation becasue from lot 1 dabranib actually continues till  14 may2015 which is after the start date of lot 2 and it again starts on anytime in lot 2 so dabrafenib will be included in lot 2 .end date would be the end date of latest use drug which is 24 jul 2015.But as a new core drug Nivolumab starts before 14jul2015.The end date would be the 1 day prior to the start dae of Nivolumab.So lot 2 ends on 13jul2015.Lot 3 starts on 14jul2015 with a lot intiative drug Nivolumab .Now we need to check drug within21 days .but we did not find any drug and so on.Althoug drug Dabrafenib and Trametinib Dimethyl Sulfoxide continues after start of lot 3 but these drugs did not start again any time within 21 or 60 days.So it will not be considered as overlapping so i will not be included in lot 3 .So lot 3ends on 14 jul2015.But currently in our code it is considering as overlapping case which should not be.   Note - If a core drug from previous lot continues on or after start of current lot and again it will start any time while checking 21 days or 60 days then the core drug or drugs from previous lot will include in current lot and end will update accordingly of the latest .E.G - Let’s assume a situation where Lot 1 ends on 1 apr 2016 .Lot 1 contains core drug C1 and non core drug N2.Both the drugs continues till 10 apr 2016.As a new core drug C3 starts on 5 apr 2015,lot 1 ends on 1 day prior to this ..Lot 1 ends on 4apr 2015..Lot2 starts on 5 th apr 2015 while checking 21 days and 60 days criteria .We did not get any other drug. So lot will have only C2.Although from lot1 core drug C1 continues till 10 apr 2015 which is a date after start of lot 2,we will not not include in C1 the lot 3 as C1 did not start again any time while checking 21 days or 60 days criteria.If we had found another start of C1 anytime while checking 21 days or 60 days criteria, it would have been included in the lot .   For patient id 874512399 - Core drug Dabrafenib starts on 25 april and within 21 days Trametinib Dimethyl Sulfoxide starts and latest use of the core drug is 28may2014. now applying 60 days criteria we get lot 1 ends on 21jan 2015.As per the rule of new lot start ethier there shold be no use of drug within 60 days or there is any new core drug starts within 60 days.Here from end date of lot1 is 21jan2015 with a blank period of more than 60 days.So lot2 starts on 16apr2015 .after applying 21 days criteris we get Bevacizumab starts within 21 days and after applying 60 days criteria there is no core drug found .so lot 2 ends on 16apr2015.To start of lot 3 ,there should be gap of blank 60 days with no drug use or a new core drug other than previous lot should be there .So applying this rule we get Bevacizumab starts on 16june2015 and applying 21 days from 16jun2015 we did not find any core or non core drug in intial 21 days.As only non core drug found in initial 21 days lot3 is non core only lot .So while checking 60 days criteria ,we need to check any non core drug from initial 21 days starts within 60 days or not .If starts the end date of this lot will increase according to the maximum end date .And then again need to check 60 days from updated end date until there is a blank period of 60 days with no drug use or a new core drug starts.Any new non core drug find in 60 days checking we can ignore the new core drug .As stated earlier ,a new core drug Pembrolizumab found while checking 60 days criteria ,the lot 3 ends on 1 day prior to start of this newly identified core drug .We get lot 3 ends on 7apr2016. Lot4 starts on 8apr2016.Although Bevacizumab continue till 8apr2015.We will not include in lot 4 as it is a non core drug . Note - If a core drug from previous lot continues on or after start of current lot and again it will start any time while checking 21 days or 60 days then the core drug or drugs from previous lot will include in current lot and end will update accordingly of the latest .E.G - Let’s assume a situation where Lot 1 ends on 1 apr 2016 .Lot 1 contains core drug C1 and non core drug N2.Both the drugs continues till 10 apr 2016.As a new core drug C3 starts on 5 apr 2015,lot 1 ends on 1 day prior to this ..Lot 1 ends on 4apr 2015..Lot2 starts on 5 apr 2015 while checking 21 days and 60 days criteria .We did not get any other drug.So lot will have only C2.Although from lot1 core drug C1 continues till 10 apr 2015 which is a date after start of lot 2,we will not include in C1 the lot 3 as C1 did not start again any time while checking 21 days or 60 days criteria. If we had found another start of C1 anytime while checking 21 days or 60 days criteria, it would have been included in the lot .   For patient id 54871  - A non core drug Fludarabine starts on 10apr2015 .Now we need to check what are other drug found in 21 days of 10apr2015 ,we get core drug Interlukin-2 .So  lot 1 will have Fludarabine  and Interlukin-2.All the other rules will  remain same. But currently in our code it is not including a non core drug if it starts before core drug in 21 days period. As per the rule we need to include whatever drugs are available in initial 21 days irrespective of core or non core .If it is core and non core lot ,then core drug will get preference .   Data  I have -    I have given a smaller data to understand the issues easily as the earlier data was bigger. As the problems are same I have given a smaller data.       data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 245689 Dabrafenib Core 14-Apr-15 14-May-15 245689 Trametinib_Dimethyl_Sulfoxide Core 6-May-15 4-Jun-15 245689 Dabrafenib Core 2-Jun-15 1-Jul-15 245689 Trametinib_Dimethyl_Sulfoxide Core 2-Jun-15 1-Jul-15 245689 Dabrafenib Core 25-Jun-15 24-Jul-15 245689 Trametinib_Dimethyl_Sulfoxide Core 25-Jun-15 24-Jul-15 245689 Nivolumab Core 14-Jul-15 14-Jul-15 874512399 Dabrafenib Core 25-Apr-14 24-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 29-Apr-14 28-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 30-May-14 28-Jun-14 874512399 Dabrafenib Core 2-Jun-14 1-Jul-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 4-Aug-14 2-Sep-14 874512399 Dabrafenib Core 5-Aug-14 3-Sep-14 874512399 Dabrafenib Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 11-Nov-14 11-Nov-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 1-Dec-14 1-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Dec-14 21-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 22-Dec-14 22-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Jan-15 21-Jan-15 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Apr-15 16-Apr-15 874512399 Bevacizumab Non_core 1-May-15 1-May-15 874512399 Bevacizumab Non_core 18-May-15 18-May-15 874512399 Bevacizumab Non_core 2-Jun-15 2-Jun-15 874512399 Bevacizumab Non_core 16-Jun-15 16-Jun-15 874512399 Bevacizumab Non_core 8-Jul-15 8-Jul-15 874512399 Bevacizumab Non_core 22-Jul-15 22-Jul-15 874512399 Bevacizumab Non_core 5-Aug-15 5-Aug-15 874512399 Bevacizumab Non_core 25-Aug-15 25-Aug-15 874512399 Bevacizumab Non_core 17-Sep-15 17-Sep-15 874512399 Bevacizumab Non_core 1-Oct-15 1-Oct-15 874512399 Bevacizumab Non_core 14-Oct-15 14-Oct-15 874512399 Bevacizumab Non_core 27-Oct-15 27-Oct-15 874512399 Bevacizumab Non_core 13-Nov-15 13-Nov-15 874512399 Bevacizumab Non_core 4-Dec-15 4-Dec-15 874512399 Bevacizumab Non_core 24-Dec-15 24-Dec-15 874512399 Bevacizumab Non_core 15-Jan-16 15-Jan-16 874512399 Bevacizumab Non_core 5-Feb-16 5-Feb-16 874512399 Bevacizumab Non_core 26-Feb-16 26-Feb-16 874512399 Bevacizumab Non_core 18-Mar-16 18-Mar-16 874512399 Bevacizumab Non_core 8-Apr-16 8-Apr-16 874512399 Pembrolizumab Core 8-Apr-16 8-Apr-16 54871 Fludarabine Non_core 10-Apr-14 10-Apr-14 54871 Fludarabine Non_core 11-Apr-14 11-Apr-14 54871 Fludarabine Non_core 12-Apr-14 12-Apr-14 54871 Interleukin-2 Core 15-Apr-14 22-Apr-14 54871 Interleukin-2 Core 16-Apr-14 16-Apr-14 54871 Interleukin-2 Core 17-Apr-14 17-Apr-14 54871 Interleukin-2 Core 18-Apr-14 18-Apr-14 54871 Interleukin-2 Core 19-Apr-14 19-Apr-14 54871 Interleukin-2 Core 20-Apr-14 20-Apr-14 54871 Interleukin-2 Core 21-Apr-14 21-Apr-14 ;     Data I want  -    Data I want -   patient_id         drug                                                                start_date         end_date           lot 245689            Dabrafenib                                                      14-Apr-15         05MAY2015        1 245689            Dabrafenib+ Trametinib Dimethyl Sulfoxide    06MAY2015     13JUL2015         2 245689            Nivolumab                                                       14JUL2015       14JUL2015         3 874512399        Dabrafenib+Trametinib Dimethyl Sulfoxide           25APR2014        21JAN2015          1 874512399        Bevacizumab+Trametinib Dimethyl Sulfoxide       16APR2015        16APR2015          2 874512399        Bevacizumab                                                         16JUN2015         07APR2016         3 874512399        Pembrolizumab                                                      08APR2016         08APR2016         4 54871              Fludarabine+Interleukin-2                                10APR2014        22APR2014       1   Data I am currently getting from the code -  patient_id             drug                                                                                        start_date         end_date           lot 245689                Dabrafenib                                                                              14-Apr-15       5-May-15              1 245689                Trametinib_Dimethyl_Sulfoxide+Dabrafenib                           6-May-15       13-Jul-15               2 245689                Nivolumab+Dabrafenib+Trametinib_Dimethyl_Sulfoxide        14-Jul-15        24-Jul-15              3 874512399         Dabrafenib+Trametinib Dimethyl Sulfoxide                             25-Apr-14        21-Jan-15             1 874512399         Trametinib Dimethyl Sulfoxide+Bevacizumab                         16-Apr-15         7-Apr-16              2 874512399         Pembrolizumab+Bevacizumab                                                 8-Apr-16         8-Apr-16               3 54871                 Interleukin-2                                                                            10APR2014        22APR2014       1   Thanks in advance.Please let me know if you have any concerns. ... View more

  Sir. For these two patient ids am not getting he desired output. I tried all possible ways to modify the codes to get the results. But i did not succeed .I have explained the logic in details to get the desired output .I will be really grateful if you kindly look into this  issues .   For patient id 245689 - A core drug Dabrafenib starts on 14 apr 2015 and we need to check what are the other drugs available within 21 days of 14 apr 2015.We do not have any other drug in 21days.So lot 1 will have Dabrafenib.The initial end date would be 14may 2015.But as a new core drug other than lot 1 starts before 14may 2015 , lot 1 ends on 1 day prior to start of Trametinib Dimethyl Sulfoxide whiich is 5may2015.Lot 2 starts on 6 th may. Now we need to check what are other drug or drugs   which starts in 21 days of 6 th may.We get did not any drug in 21 days so initial lot 2 will have Trametinib Dimethyl Sulfoxide and intial end date will be 4june2015..Now we need to check 60 days from 4june2015.We get drug dabrafenib starts on 2jun2015 .It is now a overlapping situation becasue from lot 1 dabranib actually continues till  14 may2015 which is after the start date of lot 2 and it again starts on anytime in lot 2 so dabrafenib will be included in lot 2 .end date would be the end date of latest use drug which is 24 jul 2015.But as a new core drug Nivolumab starts before 14jul2015.The end date would be the 1 day prior to the start dae of Nivolumab.So lot 2 ends on 13jul2015.Lot 3 starts on 14jul2015 with a lot intiative drug Nivolumab .Now we need to check drug within21 days .but we did not find any drug and so on.Althoug drug Dabrafenib and Trametinib Dimethyl Sulfoxide continues after start of lot 3 but these drugs did not start again any time within 21 or 60 days.So it will not be considered as overlapping so i will not be included in lot 3 .So lot 3ends on 14 jul2015.But currently in our code it is considering as overlapping case which should not be.   Note - If a core drug from previous lot continues on or after start of current lot and again it will start any time while checking 21 days or 60 days then the core drug or drugs from previous lot will include in current lot and end will update accordingly of the latest .E.G - Let’s assume a situation where Lot 1 ends on 1 apr 2016 .Lot 1 contains core drug C1 and non core drug N2.Both the drugs continues till 10 apr 2016.As a new core drug C3 starts on 5 apr 2015,lot 1 ends on 1 day prior to this ..Lot 1 ends on 4apr 2015..Lot2 starts on 5 th apr 2015 while checking 21 days and 60 days criteria .We did not get any other drug. So lot will have only C2.Although from lot1 core drug C1 continues till 10 apr 2015 which is a date after start of lot 2,we will not not include in C1 the lot 3 as C1 did not start again any time while checking 21 days or 60 days criteria.If we had found another start of C1 anytime while checking 21 days or 60 days criteria, it would have been included in the lot .   For patient id 874512399 - Core drug Dabrafenib starts on 25 april and within 21 days Trametinib Dimethyl Sulfoxide starts and latest use of the core drug is 28may2014. now applying 60 days criteria we get lot 1 ends on 21jan 2015.As per the rule of new lot start ethier there shold be no use of drug within 60 days or there is any new core drug starts within 60 days.Here from end date of lot1 is 21jan2015 with a blank period of more than 60 days.So lot2 starts on 16apr2015 .after applying 21 days criteris we get Bevacizumab starts within 21 days and after applying 60 days criteria there is no core drug found .so lot 2 ends on 16apr2015.To start of lot 3 ,there should be gap of blank 60 days with no drug use or a new core drug other than previous lot should be there .So applying this rule we get Bevacizumab starts on 16june2015 and applying 21 days from 16jun2015 we did not find any core or non core drug in intial 21 days.As only non core drug found in initial 21 days lot3 is non core only lot .So while checking 60 days criteria ,we need to chec kany drug from initial 21 days start within 60 days or not .If starts the end date of this lot will increase according to the maximum end date .And then again need to check 60 days from updated end date until there is a blank period of 60 days with no drug use or a new core drug starts.Any new non core drug find in 60 days checking we can ignore the new core drug .As stated earlier a new core drug Pembrolizumab while checking 60 days criteria ,the lot 3 ends on 1 day prior to start of this newly identified core drug .We get lot 3 ends on 7apr2016. Lot4 starts on 8apr2016.Although Bevacizumab continue till 8apr2015.We will not include in lot 4 as it is a non core drug . Note - If a core drug from previous lot continues on or after start of current lot and again it will start any time while checking 21 days or 60 days then the core drug or drugs from previous lot will include in current lot and end will update accordingly of the latest .E.G - Let’s assume a situation where Lot 1 ends on 1 apr 2016 .Lot 1 contains core drug C1 and non core drug N2.Both the drugs continues till 10 apr 2016.As a new core drug C3 starts on 5 apr 2015,lot 1 ends on 1 day prior to this ..Lot 1 ends on 4apr 2015..Lot2 starts on 5 th apr 2015 while checking 21 days and 60 days criteria .We did not get any other drug.So lot will have only C2.Although from lot1 core drug C1 continues till 10 apr 2015 which is a date after start of lot 2,we will not not include in C1 the lot 3 as C1 did not start again any time while checking 21 days or 60 days criteria. If we had found another start of C1 anytimewhile checking 21 days or 60 days criteria, it would have been included in the lot .   Data I have -    data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 245689 Dabrafenib Core 14-Apr-15 14-May-15 245689 Trametinib_Dimethyl_Sulfoxide Core 6-May-15 4-Jun-15 245689 Dabrafenib Core 2-Jun-15 1-Jul-15 245689 Trametinib_Dimethyl_Sulfoxide Core 2-Jun-15 1-Jul-15 245689 Dabrafenib Core 25-Jun-15 24-Jul-15 245689 Trametinib_Dimethyl_Sulfoxide Core 25-Jun-15 24-Jul-15 245689 Nivolumab Core 14-Jul-15 14-Jul-15 874512399 Dabrafenib Core 25-Apr-14 24-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 29-Apr-14 28-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 30-May-14 28-Jun-14 874512399 Dabrafenib Core 2-Jun-14 1-Jul-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 4-Aug-14 2-Sep-14 874512399 Dabrafenib Core 5-Aug-14 3-Sep-14 874512399 Dabrafenib Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 11-Nov-14 11-Nov-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 1-Dec-14 1-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Dec-14 21-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 22-Dec-14 22-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Jan-15 21-Jan-15 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Apr-15 16-Apr-15 874512399 Bevacizumab Non_core 1-May-15 1-May-15 874512399 Bevacizumab Non_core 18-May-15 18-May-15 874512399 Bevacizumab Non_core 2-Jun-15 2-Jun-15 874512399 Bevacizumab Non_core 16-Jun-15 16-Jun-15 874512399 Bevacizumab Non_core 8-Jul-15 8-Jul-15 874512399 Bevacizumab Non_core 22-Jul-15 22-Jul-15 874512399 Bevacizumab Non_core 5-Aug-15 5-Aug-15 874512399 Bevacizumab Non_core 25-Aug-15 25-Aug-15 874512399 Bevacizumab Non_core 17-Sep-15 17-Sep-15 874512399 Bevacizumab Non_core 1-Oct-15 1-Oct-15 874512399 Bevacizumab Non_core 14-Oct-15 14-Oct-15 874512399 Bevacizumab Non_core 27-Oct-15 27-Oct-15 874512399 Bevacizumab Non_core 13-Nov-15 13-Nov-15 874512399 Bevacizumab Non_core 4-Dec-15 4-Dec-15 874512399 Bevacizumab Non_core 24-Dec-15 24-Dec-15 874512399 Bevacizumab Non_core 15-Jan-16 15-Jan-16 874512399 Bevacizumab Non_core 5-Feb-16 5-Feb-16 874512399 Bevacizumab Non_core 26-Feb-16 26-Feb-16 874512399 Bevacizumab Non_core 18-Mar-16 18-Mar-16 874512399 Bevacizumab Non_core 8-Apr-16 8-Apr-16 874512399 Pembrolizumab Core 8-Apr-16 8-Apr-16 ;     Data I want -   patient_id         drug                                                                start_date         end_date           lot 245689            Dabrafenib                                                      14-Apr-15         05MAY2015        1 245689            Dabrafenib+ Trametinib Dimethyl Sulfoxide    06MAY2015     13JUL2015         2 245689            Nivolumab                                                       14JUL2015       14JUL2015         3 874512399        Dabrafenib+Trametinib Dimethyl Sulfoxide           25APR2014        21JAN2015          1 874512399        Bevacizumab+Trametinib Dimethyl Sulfoxide       16APR2015        16APR2015          2 874512399        Bevacizumab                                                         16JUN2015         07APR2016         3 874512399        Pembrolizumab                                                      08APR2016         08APR2016         4   Output I am currently getting  -    patient_id            drug                                                                                         start_date      end_date     lot 245689                Dabrafenib                                                                               14-Apr-15       5-May-15    1 245689                Trametinib_Dimethyl_Sulfoxide+Dabrafenib                            6-May-15       13-Jul-15     2 245689                Nivolumab+Dabrafenib+Trametinib_Dimethyl_Sulfoxide         14-Jul-15        24-Jul-15    3 874512399          Dabrafenib+Trametinib_Dimethyl_Sulfoxide                            25-Apr-14       21-Jan-15   1 874512399          Trametinib_Dimethyl_Sulfoxide+Bevacizumab                        16-Apr-15       16-Apr-15    2 874512399          Bevacizumab                                                                            16-Jun-15       16-Jun-15    3 874512399          Bevacizumab                                                                            25-Aug-15       25-Aug-15   4 874512399          Bevacizumab                                                                            27-Oct-15        13-Nov-15   5 874512399          Bevacizumab                                                                            15-Jan-16         5-Feb-16    6 874512399          Pembrolizumab                                                                          8-Apr-16          8-Apr-16     7   Thanks in advance .It will be really helpful if you can see the problem once. ... View more

Thanks for your reply. I try to understand the changes you made.It works fine for the patient id 4589759 but other ids are not matching .The rule for this analysis are below .The code you posed on 14 th July works 99% correctly only in some cases it is not giving the desired out .I have highlighted those in the red below.   LOT1 commences at the start of first core/non-core drug starting from index date. LOT1 comprises all the core/non-core drugs initiated within 21 days of the start of LOT. Three types of regimens may be created: Core drugs only Non-core drugs only Core drugs + Non-core drugs In case of Core only and Core + Non-core LOTs, a date of last use is identified by considering the latest date of last use of any of the core drugs initiated in the 21 days period. In case of Non-core only LOT, a date of last use is identified by considering the latest date of last use of any of the non-core drugs present in LOT1.   Currently it is taking only core drugs in initial 21 days which should not be the case any drug indentified irrespective of core or noncore it will be included in the lot but end date would be maximum date of latest use core drug.   If LOT1 comprises Core drugs only or Core + Non-core drugs, If any of the core drugs from LOT1 is identified in a period – from end date of most recent core drug to 60 days post date of latest use of a core drug - update the date of latest use accordingly. Again, core drugs are to be identified until 60 days post updated date of last use, and so on. This step is to be repeated until either no core drugs are identified in the given period or a core drug other than the core drugs present in LOT1 is identified.   If no core drug is identified from start date of most recent core drug to 60 days post date of latest use of a core drug, LOT1 ends at date of latest use. If a core drug other than the core drugs present in LOT1 is identified from start date of most recent core drug to 60 days post or 21 days period of date of latest use of a core drug, and, one or more core drugs from LOT1 overlap with the newly identified core drug which is not present in LOT1, then LOT1 ends one day prior to the beginning of newly identified core drug. LOT2 begins at the start of newly identified core drug. If any of the overlapping core drugs from LOT1 is further identified any time during LOT2, that drug is included in LOT2. Currently overlapping happens if any core drug from previous lot continues after start of current lot .but we need to check if the core drug continues after start of new lot and it is being start again then this rule should be applied.   none of the core drugs from LOT1 overlaps with the new core drug, end LOT1 at latest use of core drugs. LOT2 begins at the start of newly identified core drug. Non-core drugs identified during or after initial 21 days period will not affect duration of LOT1.  Currently if a lot comprises core plus non core drugs it is taking the maximum end irrespective of core or non core .But the end date should be the latest use of core drugs in this case. If LOT1 comprises non-core drugs only,   If any of the non-core drugs from LOT1 is identified in a period – from end date of most recent non-core drug to 60 days post date of latest use of a non-core drug - update the date of latest use accordingly. Again, non-core drugs from LOT1 are to be identified until 60 days post updated date of last use, and so on. This step is to be repeated until either no non-core drugs from LOT1 are identified in that period or a core drug is identified. If no non-core drug from LOT1 is identified from start date of most recent non-core drug to 60 days post date of latest use of a non-core drug, the regimen ends at date of latest use. If a core drug is identified from start date of most recent non-core drug to 60 days post date of latest use of a non-core drug, and, one or more non-core drugs from LOT1 overlap with the newly identified core drug, then LOT1 ends one day prior to beginning of the newly identified core drug. LOT2 begins at the start of newly identified core drug. none of the non-core drugs from LOT1 overlaps with the newly identified core drug, end LOT1 at latest use of non-core drugs in LOT1. LOT2 begins at the start of newly identified core drug. After the end of an LOT, next LOT can begin, at start of earliest core drug post end of LOT. at start of earliest non-core drug after 60 days post end of LOT given that no core drug precedes it post end of previous LOT.   The regimens, start dates and end dates of further LOTs are to be determined in a similar manner. Thanks in advance.Please let me know in case you have any concerns. ... View more

Sir,thanks for you reply . patient_id 14526897 the result is correct as you told. But for patient_id 897452634 ,the end date of the lot it is not correct as it is taking the maximum date non core drug .   Two situations I am not etting the correct output otherwise the code works fine for me .I need to change something in the code to get the desired output .I tried but did not get how to fix the issues .It will be really helpful  if you can look into this issues .   Two cases where I am missing out the right condition -    The overlapping rules says if there is any core drug or drugs from previous regimen continues after start of current regimen and ends after start of current and the core drugs or drug starts anytime again anytime after applying 21 days and 60 days ccriteria then the overlapping rule is applied .If core drug or core drugs from previous lot continues after start of current lot but it is not started again in 21 or 60 days then the overlapping rule will not be applicable.But currenly in our code it is considerin only the core drug continues after start of current lot but it is not considering whther it is start again within 60 days or not In case of a lot contains core and non core drug the enddate should the end date of latest use of core drug .But in our code it is not considering it.   These two patient id is having the above two issues  .I triedto explain in details why I am not getting the correct output in the below cases.   For patient id 245689 - A Cred drug Dabrafenib starts on 14 apr 2015 and we need to check what are the oter drugs available within 21 days of 14 apr 2015.We do not have any other drug in 21days.So lot 1 will have Dabrafenib.The intial end date would 14may 2015.But as a new core drug other than lot 1 starts before the lot 1 so lot 1 ends on 1 day prior to start of Trametinib Dimethyl Sulfoxide whiich is 5may2015.Lot 2 starts on 6 th may.Now we need to check what are the are therewhich starts in 21 days of 6 th may.We get did not any drug in 21 days so intial lot 2 will have Trametinib Dimethyl Sulfoxide and intial end date will be 4june2015..Now we need to check 60 dya from 4june2015.We get drug dabrafenib starts on 2jun2015 .Itis now a overlapping situation becasue from lot 1 dabranib actually ends date is 14 may205 which is after the start date of lot 2 and it again starts on anytime in lot 2 so dabrafenib will be included in lot 2 .end date would be the end date of latest use drug which is 24 jul 2015.But as a new core drug Nivolumab starts before 24jul2015.The end date would be the 1 day prior to the start dae of Nivolumab.So lot 2 ends on 13jul2015.Lot 3 starts on 14jul2015 with a lot intiative drug Nivolumab .Now we need tocheck drug within21 days .but we did not find any drug and so on.Althoug drug Dabrafenib and Trametinib Dimethyl Sulfoxide continues after start of lot 3 but these drugs did not start again any time within 21 or 60 days.So it willl not be considered as overlapping so i will not be included in lot 3 .So lot 3ends on 14 jul2015.But currentl in our code it is considering as overlaping case which should not be.     For patient id 4589759 - A non core drug Carboplatin starts on 1 oct 2013.Now weneed to check what are the drugs starts with 21 dyas of start of Carboplatin.So we get Paclitaxel along with Carboplatin starts within 21 days of start of lot initiative drug Carboplatin.So lot 1 will have carboplatin and paclitaxel.Maximum end date of these drug is 21 oct 2013.Now we need to check within 60 days after 21oct2013 any drug from lot 1 are there or not .If there then enddate will update with new date .But while checking 60 days we found a core drug Temazolomide starts on 25 oct 2013.So as per the rule of new lot start,There should be a minimum 60 days gap of no drug use or any new core drug found .So lot 1 ends on 21oct2013.Lot 2 satrts on 25oct13 witha lot inititive drug Temozolomide.Now again we need to identify drugs within 21 days of 25 oct2013.We found drug Paclitaxel and carboplatin within 21 days .So lot 2 will have Temozolomide,Paclitaxel,Carboplatin.As it is a combination of core and non core drug in the lot,the end date will be the end date of the core drug Temozolomide from where we need to check 60 days criteria.As there is no core drug from previous regimen found within days ,the lot ends on 21 nov 2013.In case of current lot is (core plus non core lot) or (core only lot ) ,then we need to check if any core drugs available from initial 21 days ,then only end date will update otherwise it will be the same while checking 60 days criteria after intial 21 days criteria.For non coreonly lot,we need tocheck if there is any non core drug from initial 21 days while checking 60 dyas criteriaa then end datewill update othewise it woill be th same .So in this case temozolomde core drug did not find within 60 days again .so end date of lot will be 21 nov 2013.But in our code currently it is updating the end date to 27 jun 2014 as non core drug is found within applying 60 days criteria which should be ignored as per the rule.So lot 3 begins on 23 jun 2014 and ends on 27 jun 2014 .We are getting the wrong date at lot 3 becoz it is taking the maximum end date of non care here it should end date of core crugs in case of only core or core plus non core regimen.   Data I have -   data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 245689 Dabrafenib Core 14-Apr-15 14-May-15 245689 Trametinib_Dimethyl_Sulfoxide Core 6-May-15 4-Jun-15 245689 Dabrafenib Core 2-Jun-15 1-Jul-15 245689 Trametinib_Dimethyl_Sulfoxide Core 2-Jun-15 1-Jul-15 245689 Dabrafenib Core 25-Jun-15 24-Jul-15 245689 Trametinib_Dimethyl_Sulfoxide Core 25-Jun-15 24-Jul-15 245689 Nivolumab Core 14-Jul-15 14-Jul-15 4589759 Carboplatin Non_core 1-Oct-13 1-Oct-13 4589759 Paclitaxel Non_core 1-Oct-13 1-Oct-13 4589759 Carboplatin Non_core 7-Oct-13 7-Oct-13 4589759 Paclitaxel Non_core 7-Oct-13 7-Oct-13 4589759 Carboplatin Non_core 14-Oct-13 14-Oct-13 4589759 Paclitaxel Non_core 14-Oct-13 14-Oct-13 4589759 Carboplatin Non_core 21-Oct-13 21-Oct-13 4589759 Paclitaxel Non_core 21-Oct-13 21-Oct-13 4589759 Temozolomide Core 25-Oct-13 21-Nov-13 4589759 Paclitaxel Non_core 30-Oct-13 30-Oct-13 4589759 Paclitaxel Non_core 5-Nov-13 5-Nov-13 4589759 Carboplatin Non_core 13-Nov-13 13-Nov-13 4589759 Paclitaxel Non_core 13-Nov-13 13-Nov-13 4589759 Carboplatin Non_core 19-Nov-13 19-Nov-13 4589759 Paclitaxel Non_core 19-Nov-13 19-Nov-13 4589759 Carboplatin Non_core 27-Nov-13 27-Nov-13 4589759 Paclitaxel Non_core 27-Nov-13 27-Nov-13 4589759 Carboplatin Non_core 4-Dec-13 4-Dec-13 4589759 Paclitaxel Non_core 4-Dec-13 4-Dec-13 4589759 Carboplatin Non_core 11-Dec-13 11-Dec-13 4589759 Paclitaxel Non_core 11-Dec-13 11-Dec-13 4589759 Paclitaxel Non_core 27-Dec-13 27-Dec-13 4589759 Paclitaxel Non_core 23-Jun-14 27-Jun-14 4589759 Dabrafenib Core 4-Aug-14 2-Sep-14 4589759 Trametinib_Dimethyl_Sulfoxide Core 4-Aug-14 2-Sep-14 4589759 Trametinib_Dimethyl_Sulfoxide Core 17-Sep-14 16-Oct-14 4589759 Dabrafenib Core 26-Sep-14 25-Oct-14 4589759 Dabrafenib Core 15-Oct-14 15-Oct-14 4589759 Dabrafenib Core 21-Nov-14 20-Dec-14 4589759 Trametinib_Dimethyl_Sulfoxide Core 21-Nov-14 20-Dec-14 ; Data I want -    patient_id         drug                                                                start_date         end_date           lot 245689            Dabrafenib                                                      14-Apr-15         05MAY2015        1 245689            Dabrafenib+ Trametinib Dimethyl Sulfoxide    06MAY2015     13JUL2015         2 245689            Nivolumab                                                       14JUL2015       14JUL2015         3 4589759          Carboplatin+Paclitaxel                                    01OCT2013      21OCT2013        1 4589759          Carboplatin+Paclitaxel+Temozolomide           25OCT2013      21NOV2013        2 4589759          Paclitaxel                                                         23JUN2014       27JUN2014        3 4589759          Dabrafenib+Trametinib Dimethyl Sulfoxide     04AUG2014      20DEC2014        4   Data I am currently getting -    patient_id         drug                                                                                  start_date         end_date           lot 245689            Dabrafenib                                                                        14-Apr-15        05MAY2015         1 245689            Trametinib Dimethyl Sulfoxide+Dabrafenib                       06MAY2015    13JUL2015          2 245689            Nivolumab+Dabrafenib+Trametinib Dimethyl Sulfoxide    14JUL2015      24JUL2015          3 4589759          Carboplatin+Paclitaxel                                                       01OCT2013     21OCT2013        1 4589759         Temozolomide+Paclitaxel+Carboplatin                               25OCT2013     27DEC2013        2 4589759          Paclitaxel                                                                            23JUN2014      27JUN2014        3 4589759          Dabrafenib+Trametinib Dimethyl Sulfoxide                        04AUG2014      20DEC2014       4 Thanks in advance.         ... View more

    Sir ,I have fixed all the issues and logic almost.The below are the cases where I tried hard but did not resolve the problem.If you can look into these once that would be a great help for me.   data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 14526897 Interferon_Alfa_2b Non_core 11-Aug-14 11-Aug-14 14526897 Interferon_Alfa_2b Non_core 12-Aug-14 12-Aug-14 14526897 Interferon_Alfa_2b Non_core 13-Aug-14 13-Aug-14 14526897 Interferon_Alfa_2b Non_core 14-Aug-14 14-Aug-14 14526897 Interferon_Alfa_2b Non_core 15-Aug-14 15-Aug-14 14526897 Interferon_Alfa_2b Non_core 18-Aug-14 18-Aug-14 14526897 Interferon_Alfa_2b Non_core 19-Aug-14 19-Aug-14 14526897 Interferon_Alfa_2b Non_core 20-Aug-14 20-Aug-14 14526897 Interferon_Alfa_2b Non_core 21-Aug-14 21-Aug-14 14526897 Interferon_Alfa_2b Non_core 22-Aug-14 22-Aug-14 14526897 Interferon_Alfa_2b Non_core 25-Aug-14 25-Aug-14 14526897 Interferon_Alfa_2b Non_core 26-Aug-14 26-Aug-14 14526897 Interferon_Alfa_2b Non_core 27-Aug-14 27-Aug-14 14526897 Interferon_Alfa_2b Non_core 28-Aug-14 28-Aug-14 14526897 Interferon_Alfa_2b Non_core 2-Sep-14 2-Sep-14 14526897 Interferon_Alfa_2b Non_core 3-Sep-14 3-Sep-14 14526897 Interferon_Alfa_2b Non_core 3-Sep-14 30-Sep-14 14526897 Interferon_Alfa_2b Non_core 4-Sep-14 4-Sep-14 14526897 Interferon_Alfa_2b Non_core 5-Sep-14 5-Sep-14 14526897 Interferon_Alfa_2b Non_core 8-Sep-14 8-Sep-14 14526897 Interferon_Alfa_2b Non_core 9-Sep-14 9-Sep-14 14526897 Interferon_Alfa_2b Non_core 13-Nov-14 10-Dec-14 14526897 Interferon_Alfa_2b Non_core 23-Dec-14 19-Jan-15 14526897 Interferon_Alfa_2b Non_core 9-Jan-15 5-Feb-15 14526897 Interferon_Alfa_2b Non_core 27-Feb-15 26-Mar-15 14526897 Interferon_Alfa_2b Non_core 17-Jun-15 14-Jul-15 897452634 Cisplatin Non_core 19-Jan-16 19-Jan-16 897452634 Dacarbazine Non_core 19-Jan-16 19-Jan-16 897452634 Ipilimumab Core 19-Jan-16 19-Jan-16 897452634 Vinblastine Non_core 19-Jan-16 19-Jan-16 897452634 Cisplatin Non_core 20-Jan-16 20-Jan-16 897452634 Vinblastine Non_core 20-Jan-16 20-Jan-16 897452634 Cisplatin Non_core 21-Jan-16 21-Jan-16 897452634 Vinblastine Non_core 21-Jan-16 21-Jan-16 897452634 Vinblastine Non_core 22-Jan-16 22-Jan-16 897452634 Cisplatin Non_core 9-Feb-16 9-Feb-16 897452634 Dacarbazine Non_core 9-Feb-16 9-Feb-16 897452634 Ipilimumab Core 9-Feb-16 9-Feb-16 897452634 Vinblastine Non_core 9-Feb-16 9-Feb-16 897452634 Cisplatin Non_core 10-Feb-16 10-Feb-16 897452634 Vinblastine Non_core 10-Feb-16 10-Feb-16 897452634 Cisplatin Non_core 11-Feb-16 11-Feb-16 897452634 Vinblastine Non_core 11-Feb-16 11-Feb-16 897452634 Vinblastine Non_core 12-Feb-16 12-Feb-16 897452634 Cisplatin Non_core 1-Mar-16 1-Mar-16 897452634 Dacarbazine Non_core 1-Mar-16 1-Mar-16 897452634 Ipilimumab Core 1-Mar-16 1-Mar-16 897452634 Vinblastine Non_core 1-Mar-16 1-Mar-16 897452634 Cisplatin Non_core 2-Mar-16 2-Mar-16 897452634 Vinblastine Non_core 2-Mar-16 2-Mar-16 897452634 Cisplatin Non_core 3-Mar-16 3-Mar-16 897452634 Vinblastine Non_core 3-Mar-16 3-Mar-16 897452634 Cisplatin Non_core 22-Mar-16 22-Mar-16 897452634 Dacarbazine Non_core 22-Mar-16 22-Mar-16 897452634 Ipilimumab Core 22-Mar-16 22-Mar-16 897452634 Vinblastine Non_core 22-Mar-16 22-Mar-16 897452634 Cisplatin Non_core 23-Mar-16 23-Mar-16 897452634 Vinblastine Non_core 23-Mar-16 23-Mar-16 897452634 Cisplatin Non_core 24-Mar-16 24-Mar-16 897452634 Vinblastine Non_core 24-Mar-16 24-Mar-16 587956 Ipilimumab Core 31-May-13 22-Aug-13 587956 Ipilimumab Core 3-Jun-13 3-Jun-13 587956 Dacarbazine Non_core 4-Jun-13 4-Jun-13 587956 Ipilimumab Core 4-Jun-13 4-Jun-13 587956 Ipilimumab Core 14-Jun-13 14-Jun-13 587956 Dacarbazine Non_core 1-Jul-13 1-Jul-13 587956 Ipilimumab Core 1-Jul-13 1-Jul-13 587956 Ipilimumab Core 3-Jul-13 3-Jul-13 587956 Dacarbazine Non_core 30-Jul-13 30-Jul-13 587956 Ipilimumab Core 30-Jul-13 30-Jul-13 587956 Dacarbazine Non_core 27-Aug-13 27-Aug-13 587956 Ipilimumab Core 27-Aug-13 27-Aug-13 587956 Ipilimumab Core 14-Oct-13 14-Oct-13 587956 Dacarbazine Non_core 15-Oct-13 15-Oct-13 9874526 Vemurafenib Core 9-May-13 7-Jun-13 9874526 Vemurafenib Core 11-Jun-13 10-Jul-13 9874526 Vemurafenib Core 13-Jun-13 13-Jun-13 9874526 Vemurafenib Core 11-Jul-13 11-Jul-13 9874526 Vemurafenib Core 13-Jul-13 11-Aug-13 9874526 Vemurafenib Core 13-Aug-13 13-Aug-13 9874526 Dabrafenib Core 19-Aug-13 17-Sep-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 19-Aug-13 17-Sep-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 10-Sep-13 10-Sep-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 18-Sep-13 17-Oct-13 9874526 Dabrafenib Core 23-Sep-13 22-Oct-13 9874526 Dabrafenib Core 8-Oct-13 8-Oct-13 9874526 Dabrafenib Core 21-Oct-13 19-Nov-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 21-Oct-13 19-Nov-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 6-Nov-13 6-Nov-13 9874526 Dabrafenib Core 18-Nov-13 17-Dec-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 18-Nov-13 17-Dec-13 9874526 Trametinib_Dimethyl_Sulfoxide Core 5-Dec-13 5-Dec-13 9874526 Dabrafenib Core 23-Dec-13 21-Jan-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 23-Dec-13 21-Jan-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 2-Jan-14 2-Jan-14 9874526 Dabrafenib Core 20-Jan-14 18-Feb-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 20-Jan-14 18-Feb-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 11-Feb-14 12-Mar-14 9874526 Dabrafenib Core 18-Feb-14 19-Mar-14 9874526 Dabrafenib Core 25-Feb-14 25-Feb-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 1-Apr-14 30-Apr-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 6-May-14 6-May-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 7-May-14 5-Jun-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 3-Jun-14 3-Jun-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 6-Jun-14 5-Jul-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 8-Jul-14 6-Aug-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 17-Jul-14 17-Jul-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 7-Aug-14 5-Sep-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 20-Aug-14 20-Aug-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 3-Sep-14 2-Oct-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 18-Sep-14 18-Sep-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 2-Oct-14 31-Oct-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 16-Oct-14 16-Oct-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 4-Nov-14 3-Dec-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 12-Nov-14 12-Nov-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 15-Dec-14 13-Jan-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 16-Dec-14 16-Dec-14 9874526 Trametinib_Dimethyl_Sulfoxide Core 13-Jan-15 11-Feb-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 10-Feb-15 10-Feb-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 19-Feb-15 20-Mar-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 19-Mar-15 19-Mar-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 14-Apr-15 13-May-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 23-Apr-15 23-Apr-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 14-May-15 12-Jun-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 11-Jun-15 10-Jul-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 30-Jun-15 30-Jun-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 28-Jul-15 28-Jul-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 29-Jul-15 27-Aug-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 26-Aug-15 26-Aug-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 2-Sep-15 1-Oct-15 9874526 Vemurafenib Core 2-Sep-15 1-Oct-15 9874526 Vemurafenib Core 24-Sep-15 24-Sep-15 9874526 Trametinib_Dimethyl_Sulfoxide Core 30-Sep-15 29-Oct-15 9874526 Vemurafenib Core 23-Oct-15 21-Nov-15 9874526 Cobimetinib Core 4-Dec-15 9-Jan-16 9874526 Cobimetinib Core 23-Dec-15 23-Dec-15 9874526 Cobimetinib Core 4-Jan-16 9-Feb-16 9874526 Ipilimumab Core 3-Feb-16 3-Feb-16 9874526 Nivolumab Core 3-Feb-16 3-Feb-16 874512399 Dabrafenib Core 25-Apr-14 24-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 29-Apr-14 28-May-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 30-May-14 28-Jun-14 874512399 Dabrafenib Core 2-Jun-14 1-Jul-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 4-Aug-14 2-Sep-14 874512399 Dabrafenib Core 5-Aug-14 3-Sep-14 874512399 Dabrafenib Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Sep-14 15-Oct-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 11-Nov-14 11-Nov-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 1-Dec-14 1-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Dec-14 21-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 22-Dec-14 22-Dec-14 874512399 Trametinib_Dimethyl_Sulfoxide Core 21-Jan-15 21-Jan-15 874512399 Trametinib_Dimethyl_Sulfoxide Core 16-Apr-15 16-Apr-15 874512399 Bevacizumab Non_core 1-May-15 1-May-15 874512399 Bevacizumab Non_core 18-May-15 18-May-15 874512399 Bevacizumab Non_core 2-Jun-15 2-Jun-15 874512399 Bevacizumab Non_core 16-Jun-15 16-Jun-15 874512399 Bevacizumab Non_core 8-Jul-15 8-Jul-15 874512399 Bevacizumab Non_core 22-Jul-15 22-Jul-15 874512399 Bevacizumab Non_core 5-Aug-15 5-Aug-15 874512399 Bevacizumab Non_core 25-Aug-15 25-Aug-15 874512399 Bevacizumab Non_core 17-Sep-15 17-Sep-15 874512399 Bevacizumab Non_core 1-Oct-15 1-Oct-15 874512399 Bevacizumab Non_core 14-Oct-15 14-Oct-15 874512399 Bevacizumab Non_core 27-Oct-15 27-Oct-15 874512399 Bevacizumab Non_core 13-Nov-15 13-Nov-15 874512399 Bevacizumab Non_core 4-Dec-15 4-Dec-15 874512399 Bevacizumab Non_core 24-Dec-15 24-Dec-15 874512399 Bevacizumab Non_core 15-Jan-16 15-Jan-16 874512399 Bevacizumab Non_core 5-Feb-16 5-Feb-16 874512399 Bevacizumab Non_core 26-Feb-16 26-Feb-16 874512399 Bevacizumab Non_core 18-Mar-16 18-Mar-16 874512399 Bevacizumab Non_core 8-Apr-16 8-Apr-16 874512399 Pembrolizumab Core 8-Apr-16 8-Apr-16 74521369845 Cisplatin Non_core 29-Jul-14 29-Jul-14 74521369845 Dacarbazine Non_core 29-Jul-14 29-Jul-14 74521369845 Vinblastine Non_core 29-Jul-14 29-Jul-14 74521369845 Cisplatin Non_core 30-Jul-14 30-Jul-14 74521369845 Vinblastine Non_core 30-Jul-14 30-Jul-14 74521369845 Cisplatin Non_core 31-Jul-14 31-Jul-14 74521369845 Vinblastine Non_core 31-Jul-14 31-Jul-14 74521369845 Cisplatin Non_core 26-Aug-14 26-Aug-14 74521369845 Dacarbazine Non_core 26-Aug-14 26-Aug-14 74521369845 Ipilimumab Core 26-Aug-14 26-Aug-14 74521369845 Vinblastine Non_core 26-Aug-14 26-Aug-14 74521369845 Cisplatin Non_core 27-Aug-14 27-Aug-14 74521369845 Vinblastine Non_core 27-Aug-14 27-Aug-14 74521369845 Cisplatin Non_core 28-Aug-14 28-Aug-14 74521369845 Vinblastine Non_core 28-Aug-14 28-Aug-14 74521369845 Cisplatin Non_core 16-Sep-14 16-Sep-14 74521369845 Dacarbazine Non_core 16-Sep-14 16-Sep-14 74521369845 Ipilimumab Core 16-Sep-14 16-Sep-14 74521369845 Vinblastine Non_core 16-Sep-14 16-Sep-14 74521369845 Cisplatin Non_core 17-Sep-14 17-Sep-14 74521369845 Vinblastine Non_core 17-Sep-14 17-Sep-14 74521369845 Cisplatin Non_core 18-Sep-14 18-Sep-14 74521369845 Vinblastine Non_core 18-Sep-14 18-Sep-14 74521369845 Vinblastine Non_core 19-Sep-14 19-Sep-14 74521369845 Cisplatin Non_core 7-Oct-14 7-Oct-14 74521369845 Dacarbazine Non_core 7-Oct-14 7-Oct-14 74521369845 Ipilimumab Core 7-Oct-14 7-Oct-14 74521369845 Vinblastine Non_core 7-Oct-14 7-Oct-14 74521369845 Cisplatin Non_core 8-Oct-14 8-Oct-14 74521369845 Vinblastine Non_core 8-Oct-14 8-Oct-14 74521369845 Cisplatin Non_core 9-Oct-14 9-Oct-14 74521369845 Vinblastine Non_core 9-Oct-14 9-Oct-14 74521369845 Vinblastine Non_core 10-Oct-14 10-Oct-14 74521369845 Cisplatin Non_core 28-Oct-14 28-Oct-14 74521369845 Dacarbazine Non_core 28-Oct-14 28-Oct-14 74521369845 Vinblastine Non_core 28-Oct-14 28-Oct-14 74521369845 Cisplatin Non_core 29-Oct-14 29-Oct-14 74521369845 Vinblastine Non_core 29-Oct-14 29-Oct-14 74521369845 Cisplatin Non_core 30-Oct-14 30-Oct-14 74521369845 Vinblastine Non_core 30-Oct-14 30-Oct-14 74521369845 Cisplatin Non_core 2-Dec-14 2-Dec-14 74521369845 Dacarbazine Non_core 2-Dec-14 2-Dec-14 74521369845 Vinblastine Non_core 2-Dec-14 2-Dec-14 74521369845 Cisplatin Non_core 3-Dec-14 3-Dec-14 74521369845 Vinblastine Non_core 3-Dec-14 3-Dec-14 74521369845 Cisplatin Non_core 4-Dec-14 4-Dec-14 74521369845 Vinblastine Non_core 4-Dec-14 4-Dec-14 4526789 Interleukin-2 Core 26-Oct-15 31-Oct-15 4526789 Interleukin-2 Core 27-Oct-15 27-Oct-15 4526789 Interleukin-2 Core 28-Oct-15 28-Oct-15 4526789 Interleukin-2 Core 29-Oct-15 29-Oct-15 4526789 Interleukin-2 Core 30-Oct-15 30-Oct-15 4526789 Interleukin-2 Core 31-Oct-15 31-Oct-15 4526789 Interferon_Alfa_2b Non_core 2-Nov-15 29-Nov-15 4526789 Interferon_Alfa_2b Non_core 16-Nov-15 20-Nov-15 4526789 Interferon_Alfa_2b Non_core 17-Nov-15 17-Nov-15 4526789 Interferon_Alfa_2b Non_core 18-Nov-15 18-Nov-15 4526789 Interferon_Alfa_2b Non_core 19-Nov-15 19-Nov-15 4526789 Interferon_Alfa_2b Non_core 20-Nov-15 20-Nov-15 4526789 Interferon_Alfa_2b Non_core 7-Dec-15 3-Jan-16 4526789 Interleukin-2 Core 7-Dec-15 11-Dec-15 4526789 Interleukin-2 Core 8-Dec-15 8-Dec-15 4526789 Interleukin-2 Core 9-Dec-15 9-Dec-15 4526789 Interleukin-2 Core 10-Dec-15 10-Dec-15 4526789 Interleukin-2 Core 11-Dec-15 11-Dec-15 4526789 Pembrolizumab Core 30-Mar-16 30-Mar-16 4526789 Pembrolizumab Core 20-Apr-16 20-Apr-16 4526789 Pembrolizumab Core 11-May-16 11-May-16 4526789 Pembrolizumab Core 18-May-16 18-May-16 4526789 Pembrolizumab Core 8-Jun-16 8-Jun-16 4526789 Pembrolizumab Core 29-Jun-16 29-Jun-16 78954526 Dabrafenib Core 25-Apr-14 24-May-14 78954526 Trametinib_Dimethyl_Sulfoxide Core 25-Apr-14 24-May-14 78954526 Dabrafenib Core 30-Apr-14 29-May-14 78954526 Trametinib_Dimethyl_Sulfoxide Core 30-Apr-14 29-May-14 78954526 Dabrafenib Core 29-May-14 27-Jun-14 78954526 Trametinib_Dimethyl_Sulfoxide Core 29-May-14 27-Jun-14 78954526 Dabrafenib Core 25-Jun-14 24-Jul-14 78954526 Trametinib_Dimethyl_Sulfoxide Core 25-Jun-14 24-Jul-14 78954526 Dabrafenib Core 18-Aug-14 16-Sep-14 78954526 Trametinib_Dimethyl_Sulfoxide Core 18-Aug-14 16-Sep-14 78954526 Dabrafenib Core 15-Sep-14 14-Oct-14 78954526 Trametinib_Dimethyl_Sulfoxide Core 15-Sep-14 14-Oct-14 78954526 Ipilimumab Core 9-Oct-14 9-Oct-14 8745120369 Dabrafenib Core 18-Aug-14 16-Sep-14 8745120369 Dabrafenib Core 20-Aug-14 18-Sep-14 8745120369 Trametinib_Dimethyl_Sulfoxide Core 26-Aug-14 24-Sep-14 8745120369 Trametinib_Dimethyl_Sulfoxide Core 23-Sep-14 21-Dec-14 8745120369 Dabrafenib Core 17-Oct-14 14-Jan-15 8745120369 Ipilimumab Core 22-Dec-14 22-Dec-14 ;     For patient id 14526897 - a non core drug Interferon Alfa-2b starts on 11aug2014 and applying 21 days we get lot 1 ends on 9 sep2014.there is no drug use or a new core drug in 60 dyas period after finishing of lot 1 .so lot 2 begins on 13nov2014 apply the same above rule we get lot 2 ends on 26mar2014.And so on. For patient id 897452634- Non core drug Cisplatin starts on 19jan2016 and apply 21 days and 60 days criteria we get end of latest use core drug is 22mar2016 so lot end here .As per the rule if there is core and non core drug in the lot the end date would be end date of latest use core drug not the end date of of laest use non core drug after apply 21 days and 60 days criteria. For patient id 587956 - a core drug Ipilumab starts on 31may2013 .applying 21 days and 60 days criteria ,we get end date of latest use core drug is 14oct2015 as lot 1 contains both core and noncore drugs. For patient id 9874526 - Lot 4 end on 2feb2015 as a new core drug other than lot 5 starts on 3rd feb.As per the overlapping rule if a core drug from previous lot continues after start of current regiemn and it will again start on anytime within 21 days or 60 dyas then this core drug will be added in the current regimen.If a core drug from previous regimen continues after starting of current regimen but it does not start again in 21 or 60 days periods of current regimen then overlapping rule will not be applied.overlapping rule is not aplied for non core drugs.In this current patient id as the non core drug Combimetinib from lot 4 continues after start of lot 4.but as it non core we will not add this in lot 5 but currently it is being added which should not be the case. For patient id 874512399 - Core drug Dabrafenib starts on 25 april and within 21 days Trametinib Dimethyl Sulfoxide starts and latest use of the core drug is 28may2014. now applying 60 days criteria we get lot 1 ends on 21jan 2015.As per the rule of new lot start ethier there shold be no use of drug within 60 days or there is any new core drug starts within 60 days.Here from end date of lot1 is 21jan2015 with a blank period of more than 60 days.So lot2 starts on 16apr2015 .after applying 21 days criteris we get Bevacizumab starts within 21 days and after applying 60 days criteria there is no core drug found .so lot 2 ends on 16apr2015.To start of lot 3 ,there should be gap of blank 60 days with no drug use or a new core drug other than previous lot should be there .So applying this rule we get Bevacizumab starts on 16june2015 and apply 21 and 60 days criteria ,we get lot 3 ends on 7apr2016(1 day prior to new core core drug ) as a new core drug other than lot3 starts on 8apr2016.Now lot4 begins and apllying rthe same rule we get it ends on 8apr2016. For patient id 74521369845 - A non core drugs starts on 29july2014 after apllying 21 and 60 days criteria we found a new core drug Ipilumab starts on 26aug20114 so lot 1 ends here.lot 2 starts on 26aug2014.after apllying 21 days we found a non core drug from previous lot contines after or on start of lot 2 and gian starts on within 60 days but we should not include it in lot as for non core drug the overlapping rules are not applied.and after applying 60 days criteria we get end date of latest use core drug is 7oct2014 so lot2 ends here. For patient id 4526789 - a core drug interlukin-2 starts on 26-oct2015 .after applying 21 days and 60 days criteria ,we end date of latest use core drus is 11 dec2015 as lot 1 is core plus non coreregimen.again as there is no drug in 60 dys period or no new core drug available,so lot 2 starts on 30mar2016 ..and apply the all rule we get lot 2 ends on 29jun2016. For patient id 78954526 - a core drug dabrafinib starts on 30apr14 .after applying 21 days and 60 criteria we get lot 1 ends on 8 th october as a new core drug other than from the lot 1 starts on 9oct.As per the overlapping rule if a core drug from previous lot continues after start of current regiemn and it will again start on anytime within 21 days or 60 dyas then this core drug will be added in the current regimen.If a core drug from previous regimen continues after starting of current regimen but it does not start again in 21 or 60 days periods of current regimen then overlapping rule will not be applied.Although core drug Dabranib and Trametinib continues after start of lot 2 but any of them did not start again anytime within 21 or 60 days.so it will not be included in lot 2 .but currently it is being included.   For patient id 8745120369 - the same thing as patinet id 78954526   Data i want -    patien_id             drug                                                                           start                           end 14526897            Interferon Alfa-2b                                                      11AUG2014            09SEP2014 14526897            Interferon_Alfa_2b                                                     13NOV2014            26MAR2015 14526897            Interferon_Alfa_2b                                                     17JUN2015             14JUL2015 897452634          Cisplatin+Dacarbazine+Ipilimumab+Vinblastine        19JAN2016             22MAR2016 587956                 Dacarbazine+Ipilimumab                                           31MAY2013            14OCT2013 9874526               Vemurafenib                                                               09MAY2013            13AUG2013 9874526               Dabrafenib+Trametinib Dimethyl Sulfoxide               19AUG2013             01SEP2015 9874526               Trametinib Dimethyl Sulfoxide+Vemurafenib             02SEP2015             21NOV2015 9874526               Cobimetinib                                                                04DEC2015             02FEB2016 9874526                Ipilimumab+Nivolumab                                              03FEB2016             03FEB2016 874512399            Dabrafenib+Trametinib Dimethyl Sulfoxide               25APR2014             21JAN2015 874512399             Bevacizumab+Trametinib Dimethyl Sulfoxide          16APR2015             16APR2015 874512399             Bevacizumab                                                            16JUN2015              07APR2016 874512399             Pembrolizumab + Bevacizumab                               08APR2016              08APR2016 74521369845         Cisplatin+Dacarbazine+Vinblastine                          29JUL2014              25AUG2014 74521369845         Cisplatin+Ipilimumab+Vinblastine                             26AUG2014             07OCT2014 4526789                 Interferon Alfa-2b+Interleukin-2                                 26OCT2015            11DEC2015 4526789                 Pembrolizumab                                                          30MAR2016            29JUN2016 78954526              Dabrafenib+Trametinib Dimethyl Sulfoxide                  25APR2014           08OCT2014 78954526              Ipilimumab                                                                    09OCT2014           09OCT2014 8745120369          Dabrafenib+Trametinib Dimethyl Sulfoxide                  18AUG2014           21DEC2014 8745120369           Ipilimumab                                                                   22DEC2014            22DEC2014   The result I am currently getting -   patien_id                 drug                                                                      start                end               Lot 14526897              Interferon_Alfa_2b                                                11-Aug-14      26-Mar-15        1 14526897              Interferon_Alfa_2b                                                17-Jun-15      14-Jul-15           2 897452634            Ipilimumab+Vinblastine+Cisplatin+Dacarbazine   19-Jan-16      24-Mar-16         1 587956                  Ipilimumab+Dacarbazine                                        31-May-13    15-Oct-13         1 9874526                Vemurafenib                                                            9-May-13      13-Aug-13        1 9874526             Dabrafenib+Trametinib Dimethyl Sulfoxide                19-Aug-13     1-Sep-15         2 9874526             Vemurafenib+Trametinib Dimethyl Sulfoxide              2-Sep-15       21-Nov-15       3 9874526             Cobimetinib                                                                 4-Dec-15       2-Feb-16         4 9874526            Ipilimumab+Cobimetinib+Nivolumab                            3-Feb-16       9-Feb-16         5 874512399        Dabrafenib+Trametinib Dimethyl Sulfoxide                  25-Apr-14      21-Jan-15       1 874512399        Trametinib Dimethyl Sulfoxide+Bevacizumab             16-Apr-15       7-Apr-16          2 874512399        Pembrolizumab+Bevacizumab                                     8-Apr-16        8-Apr-16          3 74521369845    Cisplatin+Dacarbazine+Vinblastine                             29-Jul-14       25-Aug-14        1 74521369845     Ipilimumab+Vinblastine+Cisplatin+Dacarbazine         26-Aug-14     4-Dec-14          2 4526789             Interleukin-2+Interferon Alfa-2b                                   26-Oct-15      3-Jan-16          1 4526789               Pembrolizumab                                                          30-Mar-16     29-Jun-16         2 78954526            Dabrafenib+Trametinib Dimethyl Sulfoxide                25-Apr-14      8-Oct-14           1 78954526         Ipilimumab+Dabrafenib+Trametinib Dimethyl Sulfoxide   9-Oct-14     14-Oct-14          2 8745120369     Dabrafenib+Trametinib Dimethyl Sulfoxide                   18-Aug-14     21-Dec-14          1 8745120369      Ipilimumab+Dabrafenib                                                 22-Dec-14     14-Jan-15           2   Thanks in advance. ... View more

Sir,thanks a lot for this help.I am now trying to fix where it does not match with the original result.I will post if I face any challenge. ... View more

Sir did you get a chance to look into why I am not getting the desired output in the actual data I have provided in case of overlapping case.Over lapping case being is being taken care of in the example data I have provided (The C1,N1 data) but not in actual.Also the cases where intital 21 days ,there are all non core drugs.It would be really helpful for me if you can look into this once. Thanks in advance. ... View more

Tthese are some othe cases where am not geeting the desired output.   For patient id 1345268 - Core drug Dabranib starts on 11oct2013 and within 21 days no core or non core drugs is available .Dabranib's latest end is 8nov2013.From here we need to check within 60 days any core drug from initial 21 days is available or not (this rule applies for lot where only core drugs in intial 21 days of start of lot or core and non core drugs in intial 21 days of start of lot.But for only non core drugs in intial 21 days of start of lot, we nned to look for non core drug from intial 21 days ).in this case a new core drug Trametinib_Dimethyl_Sulfoxide is started ,so lot one will be ended one day prior o start of this new core drug.(if intial 21 days ,we get onlynon core drugs then we look for a new non core drug to start a new lot ).So lot 2 starts on 8nov2013.It check agais now as per the above rule 21 days and 60 days criteria.but from last lot Dabranib end on the same day when lot 2 starts.Darbanib again starts on 3dec2014 which is a overlapping situation and this needs to be included in lot 2 and end date would be the end date of max end date of all the drugs used in respective lot.in this case Darbanib continues till 1 jan2014.so lt 2 ends here.But in our curent coding it is giving a different result. For Patient id 1345269 -A non core drug only available in initial 21 days .Soit needs to be considered I mentioned .Then check for core or non core drug within 60 days .if no core or non core drug found within 60 days then a new lot will begin.think we are missing this condition in our code .but i did not get how to handle this in the code.this is the same case for patient id 746461 and we are not getting Temozolomide in our lot. For patient id 1345270 -A core drug Dabraenib starts on 24mar14 .After applying 21 days and 60 dyas criteria we get lot1 ends on 5aug2014.A new core drug Vemurafenib starts on 7aug2014 so l0t 2 begins here .After applying 21 days and 60 dyas critera we got lot 2 ends on 5sep2014.Again a new core drug Trametinib_Dimethyl_Sulfoxide starts so lot 3 begins here and after applying 21 days and 60 days criteria we got lot 3 ends on 27 jan2014.rest lotsare matcing .Pleasehelpme to resolv these.   Please help help to resolve this.I am alomst able to repicate the code to get the desired output.Only above three cases and those i mentioned in my above post i have am missing ou.It wiould really helpful for me if  look into this .   How to handle the overlapping case a mentioned in mthis post and my previous post If a regimen start with only noncore drug in initial 21 days- need to treat in the same way wear doing for core drugs.(onlly do not need to check overlapping condion for non core drugs). If there is a gap of 60 or days or more(no core or non core drug is available in this gap ) after end of a lot and start of another instances next lot begins here. How to handle the trucation of drugs.   data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 1345268 Dabrafenib Core 11-Oct-13 8-Nov-13 1345268 Dabrafenib Core 4-Nov-13 3-Dec-13 1345268 Trametinib_Dimethyl_Sulfoxide Core 8-Nov-13 7-Dec-13 1345268 Trametinib_Dimethyl_Sulfoxide Core 2-Dec-13 31-Dec-13 1345268 Dabrafenib Core 3-Dec-13 1-Jan-14 1345269 Interferon-Alfa-2b Non_core 31-Oct-13 22-Jan-14 1345269 Vemurafenib Core 16-Apr-14 15-May-14 1345270 Dabrafenib Core 24-Mar-14 22-Apr-14 1345270 Trametinib_Dimethyl_Sulfoxide Core 24-Mar-14 22-Apr-14 1345270 Dabrafenib Core 17-Apr-14 16-May-14 1345270 Trametinib_Dimethyl_Sulfoxide Core 17-Apr-14 16-May-14 1345270 Trametinib_Dimethyl_Sulfoxide Core 21-May-14 19-Jun-14 1345270 Dabrafenib Core 7-Jul-14 5-Aug-14 1345270 Trametinib_Dimethyl_Sulfoxide Core 7-Jul-14 5-Aug-14 1345270 Vemurafenib Core 7-Aug-14 5-Sep-14 1345270 Trametinib_Dimethyl_Sulfoxide Core 7-Aug-14 5-Sep-14 1345270 Dabrafenib Core 3-Oct-14 1-Nov-14 1345270 Trametinib_Dimethyl_Sulfoxide Core 3-Oct-14 1-Nov-14 1345270 Dabrafenib Core 29-Dec-14 27-Jan-15 1345270 Temozolomide Core 31-Mar-15 27-Apr-15 1345270 Temozolomide Core 23-Apr-15 20-May-15 1345270 Temozolomide Core 28-May-15 24-Jun-15 1345270 Temozolomide Core 17-Jun-15 7-Jul-15 1345270 Trametinib_Dimethyl_Sulfoxide Core 20-Jul-15 18-Aug-15 1345270 Dabrafenib Core 20-Jul-15 18-Aug-15 1345270 Trametinib_Dimethyl_Sulfoxide Core 20-Aug-15 18-Sep-15 1345270 Dabrafenib Core 20-Aug-15 18-Sep-15 1345270 Trametinib_Dimethyl_Sulfoxide Core 24-Sep-15 23-Oct-15 1345270 Dabrafenib Core 15-Oct-15 13-Nov-15 1345270 Trametinib_Dimethyl_Sulfoxide Core 3-Nov-15 2-Dec-15 1345270 Dabrafenib Core 19-Nov-15 18-Dec-15 1345270 Trametinib_Dimethyl_Sulfoxide Core 17-Dec-15 15-Jan-16 ;   Data I want     patient_id                   drug                                                                     start                                         end 1345268                Dabrafenib                                                           11-Oct-13                                     7-Nov-13 1345268                Trametinib_Dimethyl_Sulfoxide,Dabrafenib          8-Nov-13                                      1-Jan-14 1345269                 Interferon-Alfa-2b                                                 31-Oct-13                                     22-Jan-14 1345269                 Vemurafenib                                                       16-Apr-14                                     15-May-14 1345270                Dabrafenib,Trametinib_Dimethyl_Sulfoxide         24-Mar-14                                    5-Aug-14 1345270                Vemurafenib                                                          7-Aug-14                                     5-Sep-14 1345270                Dabrafenib,Trametinib_Dimethyl_Sulfoxide          3-Oct-14                                     27-Jan-15 1345270                Temozolomide                                                       31-Mar-15                                    7-Jul-15 1345270                Dabrafenib,Trametinib_Dimethyl_Sulfoxide          20-Jul-15                                     15-Jan-16   Thanks in advance. ... View more

  data have; length patient_id $15. drug_name $50. ; input patient_id (drug_name drug_type) ($) (start end) (: date11.); format start end date9.; cards; 13213 Vemurafenib Core 12AUG2014 10SEP2014 13213 Vemurafenib Core 05SEP2014 04OCT2014 13213 Vemurafenib Core 03OCT2014 01NOV2014 13213 Vemurafenib Core 03NOV2014 02DEC2014 13213 Vemurafenib Core 08DEC2014 06JAN2015 13213 Vemurafenib Core 05JAN2015 03FEB2015 13213 Vemurafenib Core 06FEB2015 07MAR2015 13213 Vemurafenib Core 13MAR2015 11APR2015 13213 Vemurafenib Core 13APR2015 12MAY2015 13213 Vemurafenib Core 14MAY2015 12JUN2015 13213 Vemurafenib Core 11JUN2015 10JUL2015 13213 Vemurafenib Core 14JUL2015 12AUG2015 13213 Vemurafenib Core 12AUG2015 10SEP2015 13213 Vemurafenib Core 15SEP2015 14OCT2015 13213 Vemurafenib Core 13OCT2015 11NOV2015 13213 Vemurafenib Core 17NOV2015 14DEC2015 13213 Vemurafenib Core 11DEC2015 07JAN2016 13213 Vemurafenib Core 11JAN2016 07FEB2016 13213 Cobimetinib Core 20JAN2016 16FEB2016 13213 Vemurafenib Core 11FEB2016 09MAR2016 13213 Cobimetinib Core 11FEB2016 09MAR2016 13213 Vemurafenib Core 21MAR2016 17APR2016 13213 Cobimetinib Core 21MAR2016 17APR2016 13213 Vemurafenib Core 19APR2016 16MAY2016 13213 Cobimetinib Core 19APR2016 16MAY2016 13213 Vemurafenib Core 18MAY2016 14JUN2016 13213 Cobimetinib Core 18MAY2016 14JUN2016 13213 Vemurafenib Core 14JUN2016 11JUL2016 13213 Cobimetinib Core 16JUN2016 13JUL2016 1124567 Dasatinib Non_core 02JUN2014 01JUL2014 1124567 Dasatinib Non_core 11JUL2014 09AUG2014 1124567 Dasatinib Non_core 07AUG2014 05SEP2014 1124567 Dasatinib Non_core 03SEP2014 02OCT2014 1414197 Interferon-Alfa-2b Non_core 08JUN2015 05JUL2015 1414197 Interferon-Alfa-2b Non_core 11JUL2015 07AUG2015 1414197 Interferon-Alfa-2b Non_core 01AUG2015 28AUG2015 1414197 Interferon-Alfa-2b Non_core 21AUG2015 17SEP2015 1414197 Interferon-Alfa-2b Non_core 19SEP2015 16OCT2015 1414197 Interferon-Alfa-2b Non_core 26OCT2015 22NOV2015 1414197 Interferon-Alfa-2b Non_core 16NOV2015 16DEC2015 1414197 Interferon-Alfa-2b Non_core 14DEC2015 13JAN2016 1414197 Interferon-Alfa-2b Non_core 08JAN2016 07FEB2016 1414197 Interferon-Alfa-2b Non_core 09FEB2016 10MAR2016 1414197 Interferon-Alfa-2b Non_core 10MAR2016 09APR2016 1414197 Interferon-Alfa-2b Non_core 05APR2016 05MAY2016 1414197 Interferon-Alfa-2b Non_core 10MAY2016 30MAY2016 746461 PegInterferon-Alfa-2b Non_core 6-Aug-14 2-Sep-14 746461 PegInterferon-Alfa-2b Non_core 2-Sep-14 29-Sep-14 746461 PegInterferon-Alfa-2b Non_core 8-Oct-14 4-Nov-14 746461 PegInterferon-Alfa-2b Non_core 5-Dec-14 1-Jan-15 746461 PegInterferon-Alfa-2b Non_core 8-Jan-15 6-Feb-15 746461 PegInterferon-Alfa-2b Non_core 6-Feb-15 5-Mar-15 746461 Ipilimumab Core 3-Mar-15 23-Mar-15 746461 Temozolomide Core 24-Jun-16 21-Jul-16 ; Sir,thanks a lot for your valueable time  .I have to implemennt the below criterias.Please help me .   This is one extract of orginal data.     1. The longest of any drug name is TRAMETINIB DIMETHYL SULFOXIDE; 2.Max number of core drugs which can be included is 15. 3.Max number of core and non core drugs can be included is 30.   I just given a situation where all the rules can be applied .But in the original data which i posted above .There might be 3 situations. 1.There may be cases where in intitial 21 days core and non core drugs -  2.There may be cases where in intitial 21 days core  drugs only 3.There may be cases where in intitial 21 days  non core drug only    Situation 3 is not captured in current coding can you please where i need to change to capture situation 3.I noticed it after getting the original data. Rules for only non core drugs within initial 21 days are same as core drugs .this a ideal case for patient id 1124567 . Only overlapping rules is not applied for non core drugs only in initial 21 days .   one more thing i want o add in the rule for endinding of lot  if there is is  a gap of at least 60 days after ending of a lot (no  core or non core drug is found in this 60 days) then next lot will begin  Or there is a new core drug other than the drug from previous lot is identifed within 60 days  of latest use of drugs. Point 1 is not applied properly in the code I think when i was checking manually. The data I want -    Patient_id         start_date      end_date              drug                                               lot 13213            12AUG2014    19JAN2016         Vemurafenib                                     1 13213            20JAN2016     13JUL2016         Cobimetinib+Vemurafenib                 2 1124567        02JUN2014     02OCT2014         Dasatinib                                          1 1414197        08JUN2015     30MAY2016         Interferon-Alfa-2b                             1 746461          6-Aug-14         2-Mar-15             PegInterferon-Alfa-2b                        1 746461          3-Mar-15         23-Mar-15            Ipilimumab                                         2 746461       24 June 2016    21 July 2016         Temozolomide                                   3   For patient_id -13213 the first drug indentified is Vemurafenib .after applying 60 days critera we got it ends one day befor a new core drug Cobimetinib starts and lot 2 starts here .but Vemurafenib contnues till 07FEB2016 which is after start of lot 2 and again it starts on 11FEB2016 .this is an overlapping situation . overlapping rules- If any drug core drug from previous regimen contnues after starting of current lot and they are found in again any time (within 21 days or 60 days )it would be included in current lot and end date would be the end date of latest use core drug. Again 21 days and 60 days criteria to be applied to idendentify start date ,end date and drugs.So lot 2 ends on 13JUL2016. For patient id 1124567 -no core is found within 21 days so it needs to be treated the same way we treat a core drug .we need no check if there is any Dasatinib (a non core drug) withing 60 days if found a non core drug then the end date update accordings .AS there is no other core drug or no non core drugs is found within 60 days of end date of lot 1 ends here. For patient id 1414197 --no core is found within 21 days so it needs to be treated the same way we treat a core drug .we need no check if there is any Interferon-Alfa-2b (a non core drug) withing 60 days if found a non core drug then the end date update accordings .AS there is no other core drug or no non core drugs is found within 60 days of end date of lot 1 ends here. For patient_id - 746461 in intial 21 days we found only a non core drug PegInterferon-Alfa-2b .so we need to treat this as i mentioned above .then check for 60 days gap from the end date date of latest use drug in initial 21 days and it is found that PegInterferon-Alfa-2b it start on again 02SEP2014.now again 60 days criteria and so on. This step is to be repeated until either no non-core drugs from LOT1 are identified in that period or a core drug is identified. a new core drug Ipilimumab is found on 03MAR2015 so lot 1 end on 02MAR2015 .lot 2 begins on 03MAR2015.again i need to check 21 days and 60 days critera as above.after applying this we got lot 2 ends on 23MAR2015 as a new core drug Temozolomide starts on 24JUN2016.so lot 3 begins here and after applying 21 days and 60 days criteria we get lot 3 ends on 21JUL2016.     But the data i am getting after running the code- Patient_id       start_date              end_date           drug                                                              lot 13213         12AUG2014            19JAN2016            Vemurafenib+Vemurafe                              1 13213         20JAN2016             09MAR2016            Cobimetinib+Cobimeti                                2 13213         21MAR2016           13JUL2016             Vemurafenib+Vemurafe+Cobimetinib          3 1124567     02JUN2014             01JUL2014                                                                                 1 1124567     03SEP2014            02OCT2014                                                                                 2 1414197     08JUN2015            05JUL2015                                                                                  1 1414197    19SEP2015            16OCT2015                                                                                  2 1414197    08JAN2016             07FEB2016                                                                                  3 1414197    10MAY2016            30MAY2016                                                                                  4 746461     06 August 2014        02 September2014                                                                       1 746461     05December2014    01 January 2015                                                                            2 746461     03March2015           23 March 2015          Ipilimumab+Ipilimum                                   3 ... View more

Thanks for you valuable time .I can replicate this code in my actual data.In some cases it is not giving me the perect results .Below are the two cases .   Data I have -    patient_id                         drug_name      drug_type      start              end A1012             Peginterferon Alfa-2b      Non_core       6-Aug-14     2-Sep-14 A1012 Peginterferon Alfa-2b Non_core 2-Sep-14 29-Sep-14 A1012 Peginterferon Alfa-2b Non_core 8-Oct-14 4-Nov-14 A1012 Peginterferon Alfa-2b Non_core 5-Dec-14 1-Jan-15 A1012 Peginterferon Alfa-2b Non_core 8-Jan-15 6-Feb-15 A1012 Peginterferon Alfa-2b Non_core 6-Feb-15 5-Mar-15 A1012 Ipilimumab Core 3-Mar-15 23-Mar-15 A1012 Temozolomide Core 24-Jun-16 21-Jul-16 A101 Vemurafenib Core 25-Apr-14 24-May-14 A101 Vemurafenib Core 30-May-14 28-Jun-14 A101 Dabrafenib Core 2-Jul-14 31-Jul-14 A101 Dabrafenib Core 6-Aug-14 4-Sep-14 A101 Dabrafenib Core 24-Sep-14 23-Oct-14 A101 Trametinib Dimethyl Sulfoxide Core 24-Sep-14 22-Oct-14 A101 Trametinib Dimethyl Sulfoxide Core 24-Oct-14 14-Nov-14 A101 Dabrafenib Core 24-Oct-14 22-Nov-14 A101 Trametinib Dimethyl Sulfoxide Core 21-Nov-14 20-Dec-14 A101 Dabrafenib Core 21-Nov-14 20-Dec-14 A101 Dabrafenib Core 17-Dec-14 15-Jan-15 A101 Trametinib Dimethyl Sulfoxide Core 17-Dec-14 15-Jan-15 A101 Dabrafenib Core 5-Jan-15 3-Feb-15 A101 Temozolomide Core 10-Dec-15 14-Dec-15 A101 Dabrafenib Core 24-Sep-14 23-Oct-14 A101 Trametinib Dimethyl Sulfoxide Core 24-Sep-14 22-Oct-14 A101 Trametinib Dimethyl Sulfoxide Core 24-Oct-14 14-Nov-14 A101 Dabrafenib Core 24-Oct-14 22-Nov-14 A101 Trametinib Dimethyl Sulfoxide Core 21-Nov-14 20-Dec-14 A101 Dabrafenib Core 21-Nov-14 20-Dec-14 A101 Dabrafenib Core 17-Dec-14 15-Jan-15 A101 Trametinib Dimethyl Sulfoxide Core 17-Dec-14 15-Jan-15 A101 Dabrafenib Core 5-Jan-15 3-Feb-15 A101 Temozolomide Core 10-Dec-15 14-Dec-15     Data line of treatmentment I am getting hrough this code-       patient id         drug              start_LOT           end_LOT        LOT A102                                      6-Aug-14           2-Sep-14           1 A102                                      5-Dec-14           1-Jan-15            2 A102     Ipilimumab+Ipilimum 3-Mar-15      23-Mar-15             3 A101         Vemurafenib          25-Apr-14     28-Jun-14            1 A101         Dabrafenib             2-Jul-14        23-Sep-14           2 A101 Trametinib Dimethyl Sulfoxide+Dabrafenib 24-Sep-14 3-Feb-15 3 A101 Temozolomide 10-Dec-15 14-Dec-15 4   Data I want to have -    patient id             drug                        start_date         end_date        LOT A1012               Peginterferon Alfa-2b   6-Aug-14          2-Mar-15        1 A1012               Ipilimumab                   3-Mar-15           23-Mar-15      2 A1012 Temozolomide 24-Jun-16 21-Jul-16 3 A101 Vemurafenib 25-Apr-14 28-Jun-14 1 A101 Dabrafenib 2-Jul-14 23-Sep-14 2 A101 Trametinib Dimethyl Sulfoxide+Dabrafenib 24-Sep-14 3-Feb-15 3 A101 Temozolomide 10-Dec-15 14-Dec-15 4   For patient is A102- I forgot to mention that if in initial 21 days period all drugs found are non core then it will be treated same as core drugs and all rules would be applied same like core drugs.In this code we are not considering or dicard if there are no core drugs in initial 21 days  .For this reason I am getting blank lot in the result as above and wherever there are non core drug in initial 21 days or ther is only non core drug available for a patient.I did not get how to handle this in the code.Also some drug name is getting truncated..   Also if if a patient have only noncore drugs and no core drug are there. it would treated same as core drugs.   For patient is A101-   First two lot matches perfectly but lot 3 doest not match as Dabrafenib continues sometimes after stomties from the starting of lot 3. and again start anytime after the start of lot 3.this the overlapping case as i mentined aboved yesterday.The end date would end of end latest use of core drugs.   It would be really helpful if you can help me to handle above two scenarios ..Thanks in advance.   ... View more

Thanks for your advice.I will spend time on this .But in the current senario ,the start of lot 3 should be 12sep2014 because lot 2 ends on 26aug2014 and within 60 days a new core drug C1 (other than the core drug from lot 2)  is found with a start date of 12sep2014 .So lot 3 begins here and continues till 25oct2014(but in currrently it is giving 9sep2014 as start date).From the end of lot 3 I have to check 60 days criteria ,if no core drug is found then lot 4 begins .C3 will lot4 intiative drug as it is found after 60 dayswith a start date of 1jan2015.but in current coding febit is not considering this and not included in lot 4.And also the end date of lot 4 should be 7feb2015 (lt is he end end date of latest use of core drug) .but currently it is coming as blank.Please if you can help me to corrrect these . ... View more

One more thing I want to add that the current  lot ends 1 day prior to sart of new core drug only if a new core drug is identified in next lot otherwise the lot ends on end date of latest use core drug in current lot.In our example lot 1 ends on 28Jul2014 because C3 is indentified (other than the cre drugs present in lot 1).But  in case of lot2 no new core drug is foundd (after applying 21 days and 60 days criteria) ,this lot 2 will end on end date of latest use of core drug from lot 2.Here the above overlapping rules also needs to be checkd .If you can help to implement these two logic that would be great for me.Then update treatment pattern would be as below.   patient_id           drug                       start               end             lot A11                 C1+N1+C2+N2       23-Apr-14      28-Jul-14        1 A11                  C3+C1                    29-Jul-14       25-Oct-14        2 A11                 C3+C1+N1+N2        1-Jan-15       7-Feb-15       3   The logic is I have to identified whatever drug are found in first 21 days .Then i have to check if there any core drug from the intitial 21 days are available within 60 days of end date of last use of core drugs within 21 days.But if a new core drug is found (other than the core drug from the intial 21 days drugs) then the lot ends one day prior to start date of this drug  .If no new core drug is found. the lot will continue to last end date  of core drug  which is  a core drug from intial 21 days.Then we need  to check the overlapping rule.     Thanks in advance. ... View more

Thanks .The code works fine fot first line of treatment .Yes you are right .The N4 and N2 will not be included but C1 will be included because  the rule  says    If there is any overlapping case like if any core drug from previous lot   continues some days after starting of current line of treatment (Here first line of treatments ends 28-Jul-14  but latest use of C1 from first lot ends on 12Aug2014 which is after start of lot 2 )  and it is again found any time after start of second line of treatment then the second line of treatment would contain the drugs from second line of treatment along with c1 or all the overlapping drug drugs as per the rules (here it is found after intitial 21 days after starting of lot 2 .) I need to check any time if any core drugs from previous lot are present in current lot or not .The end date will be the end of latest use of any core drug.   patient_id           drug                       start               end             lot A11                 C1+N1+C2+N2       23-Apr-14      28-Jul-14        1 A11                  C3+C1                   29-Jul-14       25-Oct-14        2 A11                 C3+C1+N1+N2        1-Jan-15       7-Feb-15       3 ... View more