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

I responded to this in the original post: https://communities.sas.com/t5/Base-SAS-Programming/How-to-get-the-desired-output-data-to-determine-treatment/m-p/374192#M89558   Art, CEO, AnalystFinder.com ... View more

But the output doesn't look like what you are talking about.   data a; input Pat_id $ Diag_cd $ Diag_dt : date9.; format diag_dt date9.; cards; 001 a 15jul2009 001 b 15jul2009 001 c 15jul2009 001 a 29aug2010 001 b 06jan2014 001 d 06jan2014 001 e 15feb2015 ; run; data b; input Pat_id $ admit_dt : date9. discharg_dt : date9.; format admit_dt discharg_dt date9.; cards; 001 29aug2010 07sep2010 001 10feb2015 19feb2015 001 15jul2009 29jul2009 ; run; proc sql; create table want as select a.*,case when(sum(Diag_dt between admit_dt and discharg_dt) ne 0) then 'Y' else 'N' end as flag from a,b where a.Pat_id=b.Pat_id group by a.Pat_id,a.Diag_cd,a.Diag_dt; quit; ... View more

You have two additional potential issues involved with this code. You would create variables such as Total_Shareholders_Funds_14, Total_Shareholders_Funds_15 and so on. First potential issue that can catestrophically fail is the addition of 3 chracters may result in varaible names that exceed the 32 character limit. Depending on where you exceed the length you may end up with a varaible ending in _1 or just _ . Second the creation of multiple variables with data in the name (the two digit year) makes these values very cumbersome to deal with. Next year's data would require additonal variables and all the processing code would require adding the additonal variables. You would almost certainly be better off with either a new (single) name such as &a.wt (still with the character limit concern), leaving as is or possibly adding an actual weight variable. And then process BY Year for most things.   ... View more

First question is are your dates SAS date values or character? If proc contents shows them with format mmddyy10 or similar then the dates are okay. If not then you likely need to create a SAS date value that will sort correctly.   If I understand what you are wanting something like this: proc sort data= have; by Customer_number date_became_member; run; data want; set have; by Customer_number date_became_member; if first.customer_number then Memtype='P'; else Memtype='A'; run; ... View more