DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 23

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

[ Edited ]

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.

  1. The start date will be first use of any drug irrespective of core or no core
  2. The end date will be last end date of last use of any core drug
  3. 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.
  4. 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.
  5. 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.
  6. 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.

PROC Star
Posts: 7,467

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

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

PROC Star
Posts: 7,467

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

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;
Contributor
Posts: 23

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

[ Edited ]

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

Contributor
Posts: 23

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

[ Edited ]

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.

PROC Star
Posts: 7,467

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

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

 

Contributor
Posts: 23

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

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 .

PROC Star
Posts: 7,467

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

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

 

Contributor
Posts: 23

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

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.

 

PROC Star
Posts: 7,467

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

[ Edited ]

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

 

Contributor
Posts: 23

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

 

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 

  1. 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 
  2. 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

Contributor
Posts: 23

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

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 .

 

  1. How to handle the overlapping case a mentioned in mthis post and my previous post
  2. 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).
  3. 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.
  4. 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.

Contributor
Posts: 23

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

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.

PROC Star
Posts: 7,467

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

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;

 

 

PROC Star
Posts: 7,467

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

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

 

Ask a Question
Discussion stats
  • 26 replies
  • 393 views
  • 12 likes
  • 2 in conversation