topic Re: How to get the desired output data to determine treatment pattern? in SAS Programming

How to get the desired output data to determine treatment pattern?

ani_89 — Tue, 11 Jul 2017 12:47:56 GMT

Reposting -

If anyone can help me in this ,I will be really obliged .I have to submit this in next 15 hours.Thanks in advance.

I am having problem to create the desired output data.

I have to indentify the drugs which has been used in first 21 days and it will create initial line of treatment .but the below rules are applied to finalize the line of treatment.

The start date will be first use of any drug irrespective of core or no core
The end date will be last end date of last use of any core drug
If any core drug from initial line of treatment is found within 60 days from the end date of initial line of treatment then the end of initial line of treatment would be updated to end date of this indentified core drug. Again i have to check for 60 days gap and so on.
If a new core drug is identified (other than the core drug from initial line of treatment) before the end date of initial lot first lot will end one day before the start date of new core drug .Second line of treatment will be started from the start date of new core drug.
If there is no core drug from initial lot of treatment within 60 days of the end date of initial line of treatment, then second line of treatment will be started and the same above rule will be applied to find third or subsequent lots.
If there is any overlapping case like a core drug C1 is continue some days after starting of second line of treatment 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 .The end date will be the end of latest use of any core drug.

Data I have

patient_id drug_name drug_type start end
A11 N1 Non_core 23-Apr-14 21-May-14
A11 C1 Core 27-Apr-14 25-May-14
A11 C1 Core 3-May-14 31-May-14
A11 N1 Non_core 10-May-14 7-Jun-14
A11 C2 Core 11-May-14 8-Jun-14
A11 N2 Non_core 12-May-14 9-Jun-14
A11 C1 Core 10-Jul-14 7-Aug-14
A11 C1 Core 15-Jul-14 12-Aug-14
A11 C3 Core 29-Jul-14 26-Aug-14
A11 N4 Non_core 8-Sep-14 6-Oct-14
A11 N2 Non_core 9-Sep-14 7-Oct-14
A11 C1 Core 12-Sep-14 10-Oct-14
A11 C1 Core 15-Sep-14 13-Oct-14
A11 C1 Core 27-Sep-14 25-Oct-14
A11 C3 Core 1-Jan-15 29-Jan-15
A11 C1 Core 3-Jan-15 31-Jan-15
A11 C1 Core 5-Jan-15 2-Feb-15
A11 C1 Core 10-Jan-15 7-Feb-15
A11 N1 Non_core 15-Jan-15 12-Feb-15
A11 N2 Non_core 18-Jan-15 15-Feb-15

Data I want

patient_id drug start end lot
A11 C1+N1+C2+N2 23-Apr-14 28-Jul-14 1
A11 C3+C1+N4+N2 29-Jul-14 25-Oct-14 2
A11 C3+C1+N1+N2 1-Jan-15 15-Feb-15 3

Here lot represents line of treatment

In this data initially drugs found in first 21days are N1,c1,c1,n1,c2,n2 and the initial start date of lot 1 will be 4/23/2014 and initial end date will be the latest use of any core drug which is 6/8/2014

Next I need to check if there is any core drug from initial lot is available within 60 days of end of initial lot . c1 from lot 1 is there within 60 days .so the new end date will be updated to 8/7/2014.Again I have to look for 60 days criteria. i found again c1 is available within 60 days. So the new end date would be 8/12/2014.But as per the rule above there is a new core drug(other than the core drugs from initial lot) available before the end of initial line of treatment .so the first line of treatment will be end one day before the start date of newly indentified core drug. Then end of first lot is 7/28/2014.

Second line of treatment starts at 7/29/2014.the same above rules are applied here also to determine to end date .the drugs found in 21 days are C3, C1, N4, N2,C1.The initial end date will be 10/13/2014.There is an overlapping drug (C1) from lot 1 because latest use of C1 is 8/7/2014 which is after start of second lot and it is found again any time after start of lot 2.Then end date of second lot would be 10/25/2014.

As there is no drug found within 6o days so end of second lot, third lot will start at 1/1/2015.Same rule as above will be applied .

Any leads would be helpful.Thanks in advance.

Re: How to get the desired output data to determine treatment pattern?

art297 — Sun, 09 Jul 2017 04:12:05 GMT

The following code isn't complete, and doesn't meet all of your conditions, but it should give you enough direction to enable you to fix the incomplete or wrong parts:

data have;
  input (patient_id drug_name drug_type) ($) (start end) (: date11.);
  format start end  date9.;
  cards;
A11                        N1               Non_core   23-Apr-14   21-May-14
A11                        C1                 Core         27-Apr-14   25-May-14
A11                        C1                 Core         3-May-14   31-May-14
A11                        N1               Non_core   10-May-14   7-Jun-14
A11                        C2                  Core        11-May-14    8-Jun-14
A11                        N2               Non_core   12-May-14    9-Jun-14
A11                        C1                 Core         10-Jul-14      7-Aug-14
A11                        C1                 Core         15-Jul-14    12-Aug-14
A11                        C3                 Core         29-Jul-14     26-Aug-14
A11                        N4              Non_core     8-Sep-14       6-Oct-14
A11                        N2              Non_core     9-Sep-14      7-Oct-14
A11                        C1                Core           12-Sep-14    10-Oct-14
A11                        C1                Core           15-Sep-14    13-Oct-14
A11                        C1                Core           27-Sep-14     25-Oct-14
A11                        C3                Core            1-Jan-15        29-Jan-15
A11                        C1                 Core           3-Jan-15       31-Jan-15
A11                        C1                 Core            5-Jan-15       2-Feb-15
A11                        C1                 Core           10-Jan-15      7-Feb-15
A11                        N1              Non_core       15-Jan-15    12-Feb-15
A11                        N2              Non_core       18-Jan-15     15-Feb-15
;

data want (keep=patient_id drug start_date end_date lot);
  set have;
  by patient_id;
  retain start_date end_date drug end60;
  format start_date end_date date9.;
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
  if first.patient_id or counter eq 0 then do;
    start_date=start;
    end_date=end;
    if drug_type eq 'Core' then do;
      counter=1;
      drug=drug_name;
      end60=end+60;
    end;
    else do;
      drug='';
      counter=0;
    end;
  end;
  else if start le Start_Date+21 then do;
    if counter=0 then do;
      if drug_type eq 'Core' then do;
        drug=drug_name;
        counter=1;
        end60=end;
      end;
    end;
    else do;
      if not findw(drug,drug_name) then drug=catx('+',drug,drug_name);
      if drug_type eq 'Core' then do;
        counter+1;
        end_date=end;
      end;
    end;
  end;
  else if Start_Date le end60 and findw(drug,drug_name) and drug_type eq 'Core' then do;
    end60=end+60;
  end;
  if counter gt 0 and
     (next_start gt End60 or
     (counter gt 0 and
      not findw(drug,next_drug) and
      next_type eq 'Core' and
      next_start gt start_date+21)) or
      last.patient_id then do;
    end_date=start-1;
    lot+1;
    output;
    drug='';
    core='';
    counter=0;
  end;
run;

Art, CEO, AnalystFinder.com

Re: How to get the desired output data to determine treatment pattern?

art297 — Tue, 11 Jul 2017 14:36:26 GMT

I don't think anyone can help unless you spend some time to review suggested code and help identify where it doesn't meet your specs.

Below is a slightly revised version of the code I originally proposed. It produces the correct result for the first lot, but not the others. I have to question why you think your second lot is correct (i.e.,

patient_id           drug                       start               end             lot
A11                  C3+C1+N4+N2      29-Jul-14       25-Oct-14        2

C3 starts on 29jul2014 and no other drugs are used in the first 21 days, so I don't understand why the other drugs are included.

Here is the best I can do without further feedback:

data have ;
  input (patient_id drug_name drug_type) ($) (start end) (: date11.);
  format start end  date9.;
  cards;
A11                        N1               Non_core   23-Apr-14   21-May-14
A11                        C1                 Core         27-Apr-14   25-May-14
A11                        C1                 Core         3-May-14   31-May-14
A11                        N1               Non_core   10-May-14   7-Jun-14
A11                        C2                  Core        11-May-14    8-Jun-14
A11                        N2               Non_core   12-May-14    9-Jun-14
A11                        C1                 Core         10-Jul-14      7-Aug-14
A11                        C1                 Core         15-Jul-14    12-Aug-14
A11                        C3                 Core         29-Jul-14     26-Aug-14
A11                        N4              Non_core     8-Sep-14       6-Oct-14
A11                        N2              Non_core     9-Sep-14      7-Oct-14
A11                        C1                Core           12-Sep-14    10-Oct-14
A11                        C1                Core           15-Sep-14    13-Oct-14
A11                        C1                Core           27-Sep-14     25-Oct-14
A11                        C3                Core            1-Jan-15        29-Jan-15
A11                        C1                 Core           3-Jan-15       31-Jan-15
A11                        C1                 Core            5-Jan-15       2-Feb-15
A11                        C1                 Core           10-Jan-15      7-Feb-15
A11                        N1              Non_core       15-Jan-15    12-Feb-15
A11                        N2              Non_core       18-Jan-15     15-Feb-15
;

data want (keep=patient_id drug start_date end_date lot);
  set have;
  by patient_id;
  retain start_date end_date drug days;
  format start_date end_date date9.;

  /* look ahead at next record */
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
 
  /* set length of drug to be wide enough to fix all drugs */
  length drug $200.;
  
  /* initialize variables for first record within a given lot */
  if first.patient_id or counter eq 0 then do;
    start_date=start;
    end_date=end;
    days=21;
    counter=1;
    drug='';
    
    if drug_type eq 'Core' then drug=drug_name;
  end;
  
  /* if not first record in lot and still within 21 day period check to see if drug in drug list */
  else if days eq 21 and start le Start_Date+21 then do;
    if drug_type eq 'Core' then do;
      if not findw(drug,strip(drug_name)) then drug=catx('+',drug,drug_name);
      counter+1;
      end_date=max(end_date,end+60);
    end;
    
    /* ignore record if Non_core and no core yet in lot */
    else if not missing(drug) then do;
      if not findw(drug,strip(drug_name)) then drug=catx('+',drug,drug_name);
      counter+1;
    end;
  end;

  /* within 60 day check */
  else do;
    days=60;
    if start le end_date and findw(drug,drug_name) and drug_type eq 'Core' then do;
      counter+1;
    end;
  end;
  /* check to see if last.patient_id or next record indicates new lot */
  if last.patient_id or
     (counter gt 0 and
     days eq 60 and
     (next_start gt end_date or
      ((not findw(drug,strip(next_drug))) and
       next_type eq 'Core')))
  then do;
    if not last.patient_id then end_date=next_start-1;
    lot+1;
    output;
    counter=0;
  end;
run;

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Tue, 11 Jul 2017 18:00:01 GMT

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

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Tue, 11 Jul 2017 19:16:11 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

art297 — Tue, 11 Jul 2017 19:30:27 GMT

Adding the code to adjust or not adjust end_time is easy. Your other requirement, the one that would include C1 in lot 2, would require more work and thought then I can justify spending in this forum. I'll have to leave that to you.

data have ;
  input (patient_id drug_name drug_type) ($) (start end) (: date11.);
  format start end  date9.;
  cards;
A11                        N1               Non_core   23-Apr-14   21-May-14
A11                        C1                 Core         27-Apr-14   25-May-14
A11                        C1                 Core         3-May-14   31-May-14
A11                        N1               Non_core   10-May-14   7-Jun-14
A11                        C2                  Core        11-May-14    8-Jun-14
A11                        N2               Non_core   12-May-14    9-Jun-14
A11                        C1                 Core         10-Jul-14      7-Aug-14
A11                        C1                 Core         15-Jul-14    12-Aug-14
A11                        C3                 Core         29-Jul-14     26-Aug-14
A11                        N4              Non_core     8-Sep-14       6-Oct-14
A11                        N2              Non_core     9-Sep-14      7-Oct-14
A11                        C1                Core           12-Sep-14    10-Oct-14
A11                        C1                Core           15-Sep-14    13-Oct-14
A11                        C1                Core           27-Sep-14     25-Oct-14
A11                        C3                Core            1-Jan-15        29-Jan-15
A11                        C1                 Core           3-Jan-15       31-Jan-15
A11                        C1                 Core            5-Jan-15       2-Feb-15
A11                        C1                 Core           10-Jan-15      7-Feb-15
A11                        N1              Non_core       15-Jan-15    12-Feb-15
A11                        N2              Non_core       18-Jan-15     15-Feb-15
;

data want (keep=patient_id drug start_date end_date lot);
  set have;
  by patient_id;
  retain start_date end_date drug days;
  format start_date end_date date9.;

  /* look ahead at next record */
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
 
  /* set length of drug to be wide enough to fix all drugs */
  length drug $200.;
  
  /* initialize variables for first record within a given lot */
  if first.patient_id or counter eq 0 then do;
    start_date=start;
    end_date=end;
    days=21;
    counter=1;
    drug='';
    
    if drug_type eq 'Core' then drug=drug_name;
  end;
  
  /* if not first record in lot and still within 21 day period check to see if drug in drug list */
  else if days eq 21 and start le Start_Date+21 then do;
    if drug_type eq 'Core' then do;
      if not findw(drug,strip(drug_name)) then drug=catx('+',drug,drug_name);
      counter+1;
      end_date=max(end_date,end);
    end;
    
    /* ignore record if Non_core and no core yet in lot */
    else if not missing(drug) then do;
      if not findw(drug,strip(drug_name)) then drug=catx('+',drug,drug_name);
      counter+1;
    end;
  end;

  /* within 60 day check */
  else do;
    days=60;
    if start le end_date+60 and findw(drug,strip(drug_name)) and drug_type eq 'Core' then do;
      counter+1;
      end_date=max(end,end_date);
    end;
  end;
  
  /* check to see if last.patient_id or next record indicates new lot */
  if last.patient_id or
     (counter gt 0 and
     days eq 60 and
     (next_start gt end_date or
      ((not findw(drug,strip(next_drug))) and
       next_type eq 'Core')))
  then do;
    if next_start le end_date then end_date=next_start-1;
    lot+1;
    output;
    counter=0;
  end;
run;

Art, CEO, AnalystFinder.com

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Wed, 12 Jul 2017 00:29:03 GMT

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 .

Re: How to get the desired output data to determine treatment pattern?

art297 — Wed, 12 Jul 2017 15:13:49 GMT

The following still probably doesn't match all of your conditions, but does meet them for your examples:

data have ;
  input (patient_id drug_name drug_type) ($) (start end) (: date11.);
  format start end  date9.;
  cards;
A11                        N1               Non_core   23-Apr-14   21-May-14
A11                        C1                 Core         27-Apr-14   25-May-14
A11                        C1                 Core         3-May-14   31-May-14
A11                        N1               Non_core   10-May-14   7-Jun-14
A11                        C2                  Core        11-May-14    8-Jun-14
A11                        N2               Non_core   12-May-14    9-Jun-14
A11                        C1                 Core         10-Jul-14      7-Aug-14
A11                        C1                 Core         15-Jul-14    12-Aug-14
A11                        C3                 Core         29-Jul-14     26-Aug-14
A11                        N4              Non_core     8-Sep-14       6-Oct-14
A11                        N2              Non_core     9-Sep-14      7-Oct-14
A11                        C1                Core           12-Sep-14    10-Oct-14
A11                        C1                Core           15-Sep-14    13-Oct-14
A11                        C1                Core           27-Sep-14     25-Oct-14
A11                        C3                Core            1-Jan-15        29-Jan-15
A11                        C1                 Core           3-Jan-15       31-Jan-15
A11                        C1                 Core            5-Jan-15       2-Feb-15
A11                        C1                 Core           10-Jan-15      7-Feb-15
A11                        N1              Non_core       15-Jan-15    12-Feb-15
A11                        N2              Non_core       18-Jan-15     15-Feb-15
;

data want
 (keep=patient_id drug start_date end_date lot)
 ;
  set have;
  by patient_id;
  format start_date end_date date9.;

  /* look ahead at next record */
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
 
  /* set length of drug to be wide enough to fix all drugs */
  length drug $200.;
  array adrugs(100) $
  adrug1-adrug100
/*   _temporary_ */
 ;
  array adates(100)
  adate1-adate100;
/*   _temporary_ */
 ;
  *format adate1-adate20 date9.;
  retain start_date end_date drug days adrug: adate:;
    
  /* initialize variables for first record within a given lot */
  if first.patient_id or counter eq 0 then do;
    if first.patient_id then do;
      call missing (of adrugs(*));
      call missing (of adates(*));
      dcounter=0;
    end;
    start_date=start;
    end_date=end;
    days=21;
    counter=1;
    drug='';
          
    if drug_type eq 'Core' then do;
      drug=drug_name;
      dcounter+1;
      adrugs(dcounter)=drug_name;
      adates(dcounter)=end;
      do i=1 to dim(adrugs);
        if (not missing(adrugs(i))) and adates(i) ge start_date and adrugs(i) ne strip(drug_name) then do;
          drug=catx('+',drug,adrugs(i));
          end_date=max(end_date,adates(i));
        end;
        else do;
          call missing(adrugs(i));
          call missing(adates(i));
        end;
      end;
    end;
  end;
  
  /* if not first record in lot and still within 21 day period check to see if drug in drug list */
  else if days eq 21 and start le Start_Date+21 then do;
    if drug_type eq 'Core' then do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq adrugs(i) and adates(i) lt start_date then do;
          adates(i)=end;
        end;
      end;
      counter+1;
      end_date=max(end_date,end);
    end;
    
    /* ignore record if Non_core and no core yet in lot */
    else if not missing(drug) then do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        if drug_type='Core' then do;
          dcounter+1;
          adrugs(dcounter)=drug_name;
          adates(dcounter)=end;
        end;
      end;
      counter+1;
    end;
  end;

  /* within 60 day check */
  else do;
    days=60;
    if start le end_date+60 and findw(drug,strip(drug_name)) and drug_type eq 'Core' then do;
      counter+1;
      end_date=max(end,end_date);
      do i=1 to dim(adrugs);
        if adrugs(i) eq strip(drug_name) then do;
          adates(i)=max(adates(i),end);
        end;
      end;
    end;
  end;
*output;
  
  /* check to see if last.patient_id or next record indicates new lot */
  if last.patient_id or
     (counter gt 0 and
     days eq 60 and
     (next_start gt end_date+60 or
      ((not findw(drug,strip(next_drug))) and
       next_type eq 'Core')))
  then do;
    if not missing(next_start) and next_start le end_date then end_date=next_start-1;
    lot+1;
    output;
    counter=0;
  end;
run;

Art, CEO, AnalystFinder.com

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Wed, 12 Jul 2017 20:27:40 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

art297 — Wed, 12 Jul 2017 21:06:37 GMT

I can't tell anything from the data you posted. Post the data in the form of a data step and place it in the box you get when you click the {i} icon.

The data you posted showed patient_ids of A1012 folowed by A101, but your results were for A102 and A101.

First, the patient_IDs have to be in alpha order or the code won't work.

Second, since your drug names were a log longer and more complex than you originally indicated, all of the fields and arrays that capture them have to be modified to accept the longer lengths. However, one can't know those lengths without reviewing ALL of your actual data, thus those are answers you have to provide:

1. What is the longest of any of the drug names?

2. What is the maximum number of core drugs that might be included in a given lot

3. What is the maximum number of core and non-core drugs that might be included in a given lot.

Without those answers, and some representative test data, I can't be of any further help.

However, on further review of what you want from what you posted, why would you expect non-core drugs to be added if no core drugs were used in the first 21 days? The current logic only accepted drugs if a core drug was used in the first 21 days.

Art, CEO, AnalystFinder.com

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Thu, 13 Jul 2017 15:12:03 GMT

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

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Thu, 13 Jul 2017 21:35:13 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Fri, 14 Jul 2017 10:52:23 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

art297 — Fri, 14 Jul 2017 15:25:15 GMT

OK, I looked into it, and corrected some of the issues you mentioned, but I do have to point something out.

The purpose of the forum is to help others learn how to program in SAS. Other than the SAS staff who chime in sometime, the rest of us are just users like you and don't get paid for helping out.

As such, we're not here to do other people's work, just provide you with enough help so that you can figure the rest out on your own. Besides, you know your data, tasks and requirements better than any of us could, so the correctness and fine tuning of any code is best left to you.

Art, CEO, Analyst Finder.com

data have;
  informat patient_id $15. drug_name $30. ;
  infile cards delimiter='09'x;
  input patient_id drug_name drug_type $ (start end) (: date9.);
  format start end  date9.;
  format drug_name $30.;
  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	6Aug2014	2Sep2014
746461	PegInterferon-Alfa-2b	Non_core	2Sep2014	29Sep2014
746461	PegInterferon-Alfa-2b	Non_core	8Oct2014	4Nov2014
746461	PegInterferon-Alfa-2b	Non_core	5Dec2014	1Jan2015
746461	PegInterferon-Alfa-2b	Non_core	8Jan2015	6Feb2015
746461	PegInterferon-Alfa-2b	Non_core	6Feb2015	5Mar2015
746461	Ipilimumab	Core	3Mar2015	23Mar2015
746461	Temozolomide	Core	24Jun2016	21Jul2016
;

data want
 (keep=patient_id drug start_date end_date lot)
 ;
  set have;
  by patient_id notsorted;
  format start_date end_date date9.;

  /* look ahead at next record */
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
  next_type = ifc(last.patient_id, (.), next_type );
  next_drug = ifc(last.patient_id, (.), next_drug );
 
  /* set length of drug to be wide enough to fix all drugs */
  length drug $1000.;
  length hold_drug $1000.;
  array adrugs(100) $ 30 adrug1-adrug100;
  array adates(100) adate1-adate100;

  array hold_drugs(100) $ 30  hold_drug1-hold_drug100;
  array hold_dates(100) hold_date1-hold_date100;

 format adate1-adate20 date9.;
  retain start_date end_date drug days adrug: adate: hold_drug: hold_date:;
    
  /* initialize variables for first record within a given lot */
  if first.patient_id or counter eq 0 then do;
    if first.patient_id then do;
      lot=0;
      call missing (of adrugs(*));
      call missing (of adates(*));
      call missing (of hold_drugs(*));
      call missing (of hold_dates(*));
      dcounter=0;
    end;
    start_date=start;
    end_date=end;
    counter=1;
    drug='';
    hold_drug='';
          
    if drug_type eq 'Core' then do;
      drug=drug_name;
      dcounter+1;
      adrugs(dcounter)=drug_name;
      adates(dcounter)=end;
      do i=1 to dim(adrugs);
        if (not missing(adrugs(i))) and adates(i) ge start_date then do;
          if adrugs(i) ne strip(drug_name) then do;
            drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
        end;
        else do;
          call missing(adrugs(i));
          call missing(adates(i));
        end;
      end;
    end;
    else do;
      call missing (of hold_drugs(*));
      call missing (of hold_dates(*));
      hold_drug=drug_name;
      dcounter+1;
      hold_drugs(dcounter)=drug_name;
      hold_dates(dcounter)=end;
    end;
    if next_start gt start_date+21 or missing(next_start) then do;
      days=60;
      if missing(drug) then do;
        drug=hold_drug;
        do i=1 to dim(adrugs);
          if (not missing(adrugs(i))) and adates(i) ge start_date then do;
            drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
          else do;
            call missing(adrugs(i));
            call missing(adates(i));
          end;
          if (not missing(hold_drugs(i))) and hold_dates(i) ge start_date then do;
            drug=catx('+',drug,hold_drugs(i));
            end_date=max(end_date,hold_dates(i));
          end;
          else do;
            call missing(hold_drugs(i));
            call missing(hold_dates(i));
          end;
        end;
        call missing(of hold_drugs(*));
        call missing(of hold_dates(*));
        call missing(hold_drug);
      end;
    end;
    else days=21;
  end;
  
  
  
  /* if not first record in lot and still within 21 day period check to see if drug in drug list */
  else if days eq 21 then do;
    if drug_type eq 'Core' then do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq adrugs(i) and adates(i) lt start_date then do;
          adates(i)=end;
        end;
      end;
      counter+1;
      end_date=max(end_date,end);
    end;
    
    else if missing(drug) then do;
      if not findw(hold_drug,strip(drug_name)) then do;
        hold_drug=catx('+',hold_drug,drug_name);
        dcounter+1;
        hold_drugs(dcounter)=drug_name;
        hold_dates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq hold_drugs(i) and hold_dates(i) lt start_date then do;
          hold_dates(i)=end;
        end;
      end;
      end_date=max(end_date,end);
      counter+1;
    end;
 
    /* add Non_core if lot already has at least one core */
    else do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      counter+1;
    end;

    if next_start gt start_date+21 or missing(next_start) then do;
      days=60;
      if missing(drug) then do;
        drug=hold_drug;
        do i=1 to dim(adrugs);
          if (not missing(adrugs(i))) and adates(i) ge start_date then do;
            drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
          else do;
            call missing(adrugs(i));
            call missing(adates(i));
          end;
          if (not missing(hold_drugs(i))) and hold_dates(i) ge start_date then do;
            drug=catx('+',drug,hold_drugs(i));
            end_date=max(end_date,hold_dates(i));
          end;
          else do;
            call missing(hold_drugs(i));
            call missing(hold_dates(i));
          end;
        end;
        call missing(of hold_drugs(*));
        call missing(of hold_dates(*));
        call missing(hold_drug);
      end;
    end;
  end;

  /* within 60 day check */
  else do;
    days=60;
    if start le end_date+60 and findw(drug,strip(drug_name)) and drug_type eq 'Core' then do;
      counter+1;
      end_date=max(end,end_date);
      do i=1 to dim(adrugs);
        if adrugs(i) eq strip(drug_name) then do;
          adates(i)=max(adates(i),end);
        end;
      end;
    end;
  end;
*output;
  
  /* check to see if last.patient_id or next record indicates new lot */
  if last.patient_id or
     (counter gt 0 and
     days eq 60 and
     (next_start gt end_date+60 or
      ((not findw(drug,strip(next_drug))) and
       next_type eq 'Core')))
  then do;
    if not missing(next_start) and next_start le end_date then end_date=next_start-1;
    lot+1;
    output;
    counter=0;
  end;
run;

Re: How to get the desired output data to determine treatment pattern?

art297 — Fri, 14 Jul 2017 17:03:11 GMT

While I REALLY can't spend more time on this, I did find where the code was double entering non-core drugs in the drug list.

The following appears to match your stated want (at least for the example you provided):

/* last revised 14JUL2017 1:02 pm EDT*/
data have;
  informat patient_id $15. drug_name $30. ;
  infile cards delimiter='09'x;
  input patient_id drug_name drug_type $ (start end) (: date9.);
  format start end  date9.;
  format drug_name $30.;
  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	6Aug2014	2Sep2014
746461	PegInterferon-Alfa-2b	Non_core	2Sep2014	29Sep2014
746461	PegInterferon-Alfa-2b	Non_core	8Oct2014	4Nov2014
746461	PegInterferon-Alfa-2b	Non_core	5Dec2014	1Jan2015
746461	PegInterferon-Alfa-2b	Non_core	8Jan2015	6Feb2015
746461	PegInterferon-Alfa-2b	Non_core	6Feb2015	5Mar2015
746461	Ipilimumab	Core	3Mar2015	23Mar2015
746461	Temozolomide	Core	24Jun2016	21Jul2016
;

data want
 (keep=patient_id drug start_date end_date lot)
 ;
  set have;
  by patient_id notsorted;
  format start_date end_date date9.;

  /* look ahead at next record */
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
  next_type = ifc(last.patient_id, (.), next_type );
  next_drug = ifc(last.patient_id, (.), next_drug );
 
  /* set length of drug to be wide enough to fix all drugs */
  length drug $1000.;
  length hold_drug $1000.;
  array adrugs(100) $ 30 adrug1-adrug100;
  array adates(100) adate1-adate100;

  array hold_drugs(100) $ 30  hold_drug1-hold_drug100;
  array hold_dates(100) hold_date1-hold_date100;

 format adate1-adate20 date9.;
  retain start_date end_date drug days adrug: adate: hold_drug: hold_date:;
    
  /* initialize variables for first record within a given lot */
  if first.patient_id or counter eq 0 then do;
    if first.patient_id then do;
      lot=0;
      call missing (of adrugs(*));
      call missing (of adates(*));
      call missing (of hold_drugs(*));
      call missing (of hold_dates(*));
      dcounter=0;
    end;
    start_date=start;
    end_date=end;
    counter=1;
    drug='';
    hold_drug='';
          
    if drug_type eq 'Core' then do;
      drug=drug_name;
      dcounter+1;
      adrugs(dcounter)=drug_name;
      adates(dcounter)=end;
      do i=1 to dim(adrugs);
        if (not missing(adrugs(i))) and adates(i) ge start_date then do;
          if adrugs(i) ne strip(drug_name) then do;
            drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
        end;
        else do;
          call missing(adrugs(i));
          call missing(adates(i));
        end;
      end;
    end;
    else do;
      call missing (of hold_drugs(*));
      call missing (of hold_dates(*));
      hold_drug=drug_name;
      dcounter+1;
      hold_drugs(dcounter)=drug_name;
      hold_dates(dcounter)=end;
    end;
    if next_start gt start_date+21 or missing(next_start) then do;
      days=60;
      if missing(drug) then do;
        drug=hold_drug;
        do i=1 to dim(adrugs);
          if (not missing(adrugs(i))) and adates(i) ge start_date then do;
            if strip(drug_name) ne adrugs(i) then drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
          else do;
            call missing(adrugs(i));
            call missing(adates(i));
          end;
          if (not missing(hold_drugs(i))) and hold_dates(i) ge start_date then do;
            if strip(drug_name) ne hold_drugs(i) thend rug=catx('+',drug,hold_drugs(i));
            end_date=max(end_date,hold_dates(i));
          end;
          else do;
            call missing(hold_drugs(i));
            call missing(hold_dates(i));
          end;
        end;
        call missing(of hold_drugs(*));
        call missing(of hold_dates(*));
        call missing(hold_drug);
      end;
    end;
    else days=21;
  end;
  
  
  
  /* if not first record in lot and still within 21 day period check to see if drug in drug list */
  else if days eq 21 then do;
    if drug_type eq 'Core' then do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq adrugs(i) and adates(i) lt start_date then do;
          adates(i)=end;
        end;
      end;
      counter+1;
      end_date=max(end_date,end);
    end;
    
    else if missing(drug) then do;
      if not findw(hold_drug,strip(drug_name)) then do;
        hold_drug=catx('+',hold_drug,drug_name);
        dcounter+1;
        hold_drugs(dcounter)=drug_name;
        hold_dates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq hold_drugs(i) and hold_dates(i) lt /*start_date*/ end then do;
          hold_dates(i)=end;
        end;
      end;
      end_date=max(end_date,end);
      counter+1;
    end;
 
    /* add Non_core if lot already has at least one core */
    else do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      counter+1;
    end;

    if next_start gt start_date+21 or missing(next_start) then do;
      days=60;
      if missing(drug) then do;
        drug=hold_drug;
        do i=1 to dim(adrugs);
          if (not missing(adrugs(i))) and adates(i) ge start_date then do;
/*             drug=catx('+',drug,adrugs(i)); */
            end_date=max(end_date,adates(i));
          end;
          else do;
            call missing(adrugs(i));
            call missing(adates(i));
          end;
          if (not missing(hold_drugs(i))) and hold_dates(i) ge start_date then do;
/*             drug=catx('+',drug,hold_drugs(i)); */
            end_date=max(end_date,hold_dates(i));
          end;
          else do;
            call missing(hold_drugs(i));
            call missing(hold_dates(i));
          end;
        end;
        call missing(of hold_drugs(*));
        call missing(of hold_dates(*));
        call missing(hold_drug);
      end;
    end;
  end;

  /* within 60 day check */
  else do;
    days=60;
    if start le end_date+60 and findw(drug,strip(drug_name)) /*and drug_type eq 'Core'*/ then do;
      counter+1;
      end_date=max(end,end_date);
      do i=1 to dim(adrugs);
        if adrugs(i) eq strip(drug_name) then do;
          adates(i)=max(adates(i),end);
        end;
      end;
    end;
  end;
*output;
  
  /* check to see if last.patient_id or next record indicates new lot */
  if last.patient_id or
     (counter gt 0 and
     days eq 60 and
     (next_start gt end_date+60 or
      ((not findw(drug,strip(next_drug))) and
       next_type eq 'Core')))
  then do;
    if not missing(next_start) and next_start le end_date then end_date=next_start-1;
    lot+1;
    output;
    counter=0;
  end;
run;

Art, CEO, AnalystFinder.com

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Sun, 16 Jul 2017 20:58:03 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Thu, 20 Jul 2017 22:25:12 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

art297 — Thu, 20 Jul 2017 22:31:49 GMT

The code is producing some results you're not expecting because it is only implementing the rules as I understand them.

e.g. patient_id 14526897 got his/her drug again on Sep 3rd with an expiration date of 30sep14, As such, the sixty day window kept increasing un 26mar15.

for patient_id 897452634, Ipilimumab was the first drug counted as it was a core drug identified within the first 21 days.

As for the ending dates, you'll have to review and understand the code to ensure that your number of days criteria are met. That is the main place where the current code might be wrong.

Art, CEO, AnalystFinder.com

Re: How to get the desired output data to determine treatment pattern?

ani_89 — Fri, 21 Jul 2017 21:38:25 GMT

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.

Re: How to get the desired output data to determine treatment pattern?

art297 — Sat, 22 Jul 2017 16:43:09 GMT

Based on one of your responses I had removed the core requirement to increase dates. It's included, again, below.

The analysis appears to correctly deal with patient 4589759.

I don't recall ever seeing data for patient 245689, thus can't respond:

/* last revised 21JUL2017 1:02 pm EDT, but 14JUL2017 was better*/
data have;
length patient_id $15. drug_name $50. ;
infile cards delimiter='09'x;
input patient_id (drug_name drug_type) ($) (start end) (: date11.);
  format start end  date9.;
/* 14526897	Interferon_Alfa_2b	Non_core	3-Sep-14	30-Sep-14 */
  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	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
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 want
 (keep=patient_id drug start_date end_date lot)
 ;
  set have;
  by patient_id notsorted;
  format start_date end_date date9.;

  /* look ahead at next record */
  set have ( firstobs = 2 keep = start drug_type drug_name 
              rename = (start = next_start drug_type=next_type drug_name=next_drug) )
      have (      obs = 1 drop = _all_ );
  next_start = ifn(last.patient_id, (.), next_start );
  next_type = ifc(last.patient_id, (.), next_type );
  next_drug = ifc(last.patient_id, (.), next_drug );
 
  /* set length of drug to be wide enough to fix all drugs */
  length drug $1000.;
  length hold_drug $1000.;
  array adrugs(100) $ 30 adrug1-adrug100;
  array adates(100) adate1-adate100;

  array hold_drugs(100) $ 30  hold_drug1-hold_drug100;
  array hold_dates(100) hold_date1-hold_date100;

 format adate1-adate20 date9.;
  retain start_date end_date drug days adrug: adate: hold_drug: hold_date:;
    
  /* initialize variables for first record within a given lot */
  if first.patient_id or counter eq 0 then do;
    if first.patient_id then do;
      lot=0;
      call missing (of adrugs(*));
      call missing (of adates(*));
      call missing (of hold_drugs(*));
      call missing (of hold_dates(*));
      dcounter=0;
    end;
    start_date=start;
    end_date=end;
    counter=1;
    drug='';
    hold_drug='';
          
    if drug_type eq 'Core' then do;
      drug=drug_name;
      dcounter+1;
      adrugs(dcounter)=drug_name;
      adates(dcounter)=end;
      do i=1 to dim(adrugs);
        if (not missing(adrugs(i))) and adates(i) ge start_date then do;
          if adrugs(i) ne strip(drug_name) then do;
            drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
        end;
        else do;
          call missing(adrugs(i));
          call missing(adates(i));
        end;
      end;
    end;
    else do;
      call missing (of hold_drugs(*));
      call missing (of hold_dates(*));
      hold_drug=drug_name;
      dcounter+1;
      hold_drugs(dcounter)=drug_name;
      hold_dates(dcounter)=end;
    end;
    if next_start gt start_date+21 or missing(next_start) then do;
      days=60;
      if missing(drug) then do;
        drug=hold_drug;
        do i=1 to dim(adrugs);
          if (not missing(adrugs(i))) and adates(i) ge start_date then do;
            if strip(drug_name) ne adrugs(i) then drug=catx('+',drug,adrugs(i));
            end_date=max(end_date,adates(i));
          end;
          else do;
            call missing(adrugs(i));
            call missing(adates(i));
          end;
          if (not missing(hold_drugs(i))) and hold_dates(i) ge start_date then do;
            if strip(drug_name) ne hold_drugs(i) then rug=catx('+',drug,hold_drugs(i));
            end_date=max(end_date,hold_dates(i));
          end;
          else do;
            call missing(hold_drugs(i));
            call missing(hold_dates(i));
          end;
        end;
        call missing(of hold_drugs(*));
        call missing(of hold_dates(*));
        call missing(hold_drug);
      end;
    end;
    else days=21;
  end;
  
  
  
  /* if not first record in lot and still within 21 day period check to see if drug in drug list */
  else if days eq 21 then do;
    if drug_type eq 'Core' then do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq adrugs(i) and adates(i) lt start_date then do;
          adates(i)=end;
        end;
      end;
      counter+1;
      end_date=max(end_date,end);
    end;
    
    else if missing(drug) then do;
      if not findw(hold_drug,strip(drug_name)) then do;
        hold_drug=catx('+',hold_drug,drug_name);
        dcounter+1;
        hold_drugs(dcounter)=drug_name;
        hold_dates(dcounter)=end;
      end;
      do i=1 to dim(adrugs);
        if drug_name eq hold_drugs(i) and hold_dates(i) lt /*start_date*/ end then do;
          hold_dates(i)=end;
        end;
      end;
      end_date=max(end_date,end);
      counter+1;
    end;
 
    /* add Non_core if lot already has at least one core */
    else do;
      if not findw(drug,strip(drug_name)) then do;
        drug=catx('+',drug,drug_name);
        dcounter+1;
        adrugs(dcounter)=drug_name;
        adates(dcounter)=end;
      end;
      counter+1;
    end;

    if next_start gt start_date+21 or missing(next_start) then do;
      days=60;
      if missing(drug) then do;
        drug=hold_drug;
        do i=1 to dim(adrugs);
          if (not missing(adrugs(i))) and adates(i) ge start_date then do;
/*             drug=catx('+',drug,adrugs(i)); */
            end_date=max(end_date,adates(i));
          end;
          else do;
            call missing(adrugs(i));
            call missing(adates(i));
          end;
          if (not missing(hold_drugs(i))) and hold_dates(i) ge start_date then do;
/*             drug=catx('+',drug,hold_drugs(i)); */
            end_date=max(end_date,hold_dates(i));
          end;
          else do;
            call missing(hold_drugs(i));
            call missing(hold_dates(i));
          end;
        end;
        call missing(of hold_drugs(*));
        call missing(of hold_dates(*));
        call missing(hold_drug);
      end;
    end;
  end;

  /* within 60 day check */
  else do;
    days=60;
/*     if start le end_date+60 and findw(drug,strip(drug_name)) /* and drug_type eq 'Core'* / then do; */
    if start le end_date+60 and findw(drug,strip(drug_name)) and drug_type eq 'Core' then do;
      counter+1;
      end_date=max(end,end_date);
      do i=1 to dim(adrugs);
        if adrugs(i) eq strip(drug_name) then do;
          adates(i)=max(adates(i),end);
        end;
      end;
    end;
  end;
*output;
  
  /* check to see if last.patient_id or next record indicates new lot */
  if last.patient_id or
     (counter gt 0 and
     days eq 60 and
     (next_start gt end_date+60 or
      ((not findw(drug,strip(next_drug))) and
       next_type eq 'Core')))
  then do;
    if not missing(next_start) and next_start le end_date then end_date=next_start-1;
    lot+1;
    output;
    counter=0;
  end;
run;

Art, CEO, AnalystFinder.com