How to create a report by assigning Start and End Date

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to create a report by assigning Start and End Date

[ Edited ]

Hi All,

I am facing the problem in assigning start and end date in each drug usage in the following data set, I don't know how should I do this, please help me on this, 

 

data Mediciene;
   input PTID $ 1-8 Dayssup 10-11 @13 Drug_dt date11. Drug $23-36;
   format Drug_dt date9.;
   datalines;
21847081 28	16-Oct-08 dexamethasone
21847081 28	04-Dec-08 dexamethasone
21847081 30	30-Dec-08 dexamethasone
21847081 28	01-Feb-09 dexamethasone
21847081 30	10-Aug-10 dexamethasone
21847081 30	24-Sep-10 dexamethasone
21847081 30	06-Nov-10 dexamethasone
21847081 28	07-Jun-12 dexamethasone
21847081 28	06-Jul-12 dexamethasone
21847081 28	14-Aug-12 dexamethasone
21847081 28	14-Sep-12 dexamethasone
21847081 28	28-Oct-12 dexamethasone
21847081 28	06-Dec-02 dexamethasone
21847081 28	18-Jan-13 dexamethasone
21847081 21	24-Sep-10 Lenalidomide
21847081 21	20-Oct-10 Lenalidomide
21847081 21	18-Nov-10 Lenalidomide
21847081 21	16-Dec-10 Lenalidomide
21847081 21	18-Jan-11 Lenalidomide
21847081 21	10-Feb-11 Lenalidomide
21847081 21	14-Mar-11 Lenalidomide
21847081 21	01-Jun-12 Lenalidomide
21847081 21	26-Jun-12 Lenalidomide
21847081 21	19-Jul-12 Lenalidomide
21847081 21	23-Aug-12 Lenalidomide
21847081 21	18-Sep-12 Lenalidomide
21847081 21	19-Oct-12 Lenalidomide
21847081 21	14-Nov-12 Lenalidomide
21847081 21	13-Dec-12 Lenalidomide
21847081 21	14-Jan-13 Lenalidomide
21847081 90	12-Mar-13 prednisolone
21847081 89	25-Jun-13 prednisolone
21847081 28	15-Oct-08 thalidomide
21847081 28	04-Dec-08 thalidomide
21847081 28	05-Jan-09 thalidomide
21847081 28	28-Jan-09 thalidomide
21847081 28	02-Mar-09 thalidomide
21847081 28	27-Mar-09 thalidomide
;

 

 Business Logic:- Treatment period for a drug would be the period from the first date of use to the last date of use. The latter is the date when there is at least a 60/30/90 day gap(Here the gap is 90 days for dexamethasone and 60 days for all other drugs) between the end date of usage of one claim (Start date + days supply) and the claim date of the next claim of the same drug.

 

Example -

 

PTID            Drug_dt                Drug                     Startdate + days supply       Gap
21847081   28 16-Oct-08     dexamethasone          12-nov-08                               22
21847081   28 04-Dec-08    dexamethasone           31-dec-08                               -1
21847081   30 30-Dec-08    dexamethasone           28-jan-09                                 4
21847081   28 01-Feb-09    dexamethasone           28-feb-09                               528
21847081   30 10-Aug-10    dexamethasone           08-sep-10                                16

 

For dexamethasone in above table, the gap days is less than 90 till row 4. so it will become a single regimn containing dexamethasone with start date of 16-Oct-08 and end date of 28-Feb-09. And there will be a different start date of 10-Aug-10 as there is gap of more than 90 days. Applying the same logic in all data, we get final table (desired Output).

 

I want to create desired output like this(Below), but I am unabling to do so,

 

Output data Set (Desired Output):-

 

PTID         Dayssup       Drug                    Stdt               enddt
21847081   30            dexamethasone   16Oct2008    28Feb2009
21847081   28            dexamethasone   10Aug2010   05Dec2010
21847081   28            dexamethasone   07jun2012    14feb2013
21847081   21            Lenalidomide       24sep2010    03apr2011
21847081   21            Lenalidomide       01jun2012    03feb2013
21847081   89            prednisolone       12mar2013    21sep2013
21847081   28            thalidomide         15Oct2008     23apr2009

 


Accepted Solutions
Solution
Tuesday
Valued Guide
Posts: 580

Re: How to create a report by assigning Start and End Date

Posted in reply to umeshgiri48

There seems to be a mistake in the data provided, the year in in line

21847081 28 06-Dec-02 dexamethasone

should be (20)12, not (20)02, right?

 

If you want to get a solution yourself, you will want to look for documentation explaining

  • look-ahead technique.
  • by-groups
  • first/last
  • date calculations
  • retain-statement
  • output-statement

The following solution needs further testing before i would call it stable.

 

Spoiler
data work.want;
   if eof = 0 then do;
      set work.Mediciene(firstobs= 2 keep= Drug_dt rename=(Drug_dt=NextStart)) end=eof;
   end;

   set work.Mediciene;
   by ptid Drug notsorted;

   length StartDate EndDate 8 outmarker 8;
   format StartDate EndDate date9.;
   retain StartDate;

   EndDate = drug_dt + dayssup -1;

   if first.Drug then do;
      NextStart = EndDate;
      StartDate = drug_dt;
   end; 

   Gap = NextStart - EndDate;
   outmarker = 0;

   if lowcase(Drug) = 'dexamethasone' then do;
      if Gap > 90 then do;
         outmarker = 1;
      end;
   end;
   else do;
      if Gap > 60 then do;
         outmarker = 2;
      end;
   end;

   if last.drug then do;
      outmarker = 3;
   end;


   if outmarker > 0 then do;
      output;
      StartDate = NextStart;
   end;

   drop outmarker Drug_dt Gap;
run;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

Re: How to create a report by assigning Start and End Date

Posted in reply to umeshgiri48

Post test data in the form of a datastep.

Post required output in the body of the text of the post.

Explain the logic between the two.

Show what code you have written and where you are stuck.

New Contributor
Posts: 4

Re: How to create a report by assigning Start and End Date

Hi,

I have edited the code as you have asked, I don't know what code should I write so that I can get a desired output, can you help me on this?

Regular Contributor
Posts: 213

Re: How to create a report by assigning Start and End Date

Posted in reply to umeshgiri48

And now you have to describe the logic linking both datasets.

New Contributor
Posts: 4

Re: How to create a report by assigning Start and End Date

Posted in reply to error_prone

Hi,

Now I have defined the logic between the both the data sets, have a look and help me on this.

Respected Advisor
Posts: 3,055

Re: How to create a report by assigning Start and End Date

[ Edited ]
Posted in reply to umeshgiri48

Business Logic:- Treatment period for a drug would be the period from the first date of use to the last date of use. The latter is the date when there is at least a 60/30/90 day gap between the end date of usage of one claim and the claim date of the next claim of the same drug

 

But we don't know what this means: "at least a 60/30/90 day gap between the end date of usage of one claim and the claim date of the next claim of the same drug". What is a 60/30/90 day gap? We know what a 60 day gap mans. We know what a 30 day gap means. We know what a 90 day gap means. We don't know what a 60/30/90 day gap is.


Does the variable DAYSSUP have any role to play here? If so, explain.

--
Paige Miller
New Contributor
Posts: 4

Re: How to create a report by assigning Start and End Date

Posted in reply to PaigeMiller

Hi,

 

I have uploaded the whole case study with all the details, can you please help me on this!

Respected Advisor
Posts: 3,055

Re: How to create a report by assigning Start and End Date

[ Edited ]
Posted in reply to umeshgiri48

I think this will do it. Note: I have fixed a typo in your original data. Assumes data is sorted by patient and drug and drug_dt.

 

data Mediciene;
   input PTID $ 1-8 Dayssup 10-11 @13 Drug_dt date11. Drug $23-36;
   end_of_supply=intnx('days',drug_dt,dayssup)-1;
   prev_drug=lag(drug);
   prev_end_of_supply=lag(end_of_supply);
   gap=drug_dt-prev_end_of_supply;
   if drug^=prev_drug then group=0;
   if drug=prev_drug and ((drug='dexamethasone' and gap>90) or
        (drug^='dexamethasone' and gap>60)) then group+1;
   format Drug_dt end_of_supply date9.;
   datalines;
21847081 28	16-Oct-08 dexamethasone
21847081 28	04-Dec-08 dexamethasone
21847081 30	30-Dec-08 dexamethasone
21847081 28	01-Feb-09 dexamethasone
21847081 30	10-Aug-10 dexamethasone
21847081 30	24-Sep-10 dexamethasone
21847081 30	06-Nov-10 dexamethasone
21847081 28	07-Jun-12 dexamethasone
21847081 28	06-Jul-12 dexamethasone
21847081 28	14-Aug-12 dexamethasone
21847081 28	14-Sep-12 dexamethasone
21847081 28	28-Oct-12 dexamethasone
21847081 28	06-Dec-12 dexamethasone
21847081 28	18-Jan-13 dexamethasone
21847081 21	24-Sep-10 Lenalidomide
21847081 21	20-Oct-10 Lenalidomide
21847081 21	18-Nov-10 Lenalidomide
21847081 21	16-Dec-10 Lenalidomide
21847081 21	18-Jan-11 Lenalidomide
21847081 21	10-Feb-11 Lenalidomide
21847081 21	14-Mar-11 Lenalidomide
21847081 21	01-Jun-12 Lenalidomide
21847081 21	26-Jun-12 Lenalidomide
21847081 21	19-Jul-12 Lenalidomide
21847081 21	23-Aug-12 Lenalidomide
21847081 21	18-Sep-12 Lenalidomide
21847081 21	19-Oct-12 Lenalidomide
21847081 21	14-Nov-12 Lenalidomide
21847081 21	13-Dec-12 Lenalidomide
21847081 21	14-Jan-13 Lenalidomide
21847081 90	12-Mar-13 prednisolone
21847081 89	25-Jun-13 prednisolone
21847081 28	15-Oct-08 thalidomide
21847081 28	04-Dec-08 thalidomide
21847081 28	05-Jan-09 thalidomide
21847081 28	28-Jan-09 thalidomide
21847081 28	02-Mar-09 thalidomide
21847081 28	27-Mar-09 thalidomide
;

proc summary nway data=mediciene;
	class ptid drug group;
	var drug_dt end_of_supply;
	output out=want min(drug_dt)=stdt max(end_of_supply)=enddt;
run;

 

--
Paige Miller
Solution
Tuesday
Valued Guide
Posts: 580

Re: How to create a report by assigning Start and End Date

Posted in reply to umeshgiri48

There seems to be a mistake in the data provided, the year in in line

21847081 28 06-Dec-02 dexamethasone

should be (20)12, not (20)02, right?

 

If you want to get a solution yourself, you will want to look for documentation explaining

  • look-ahead technique.
  • by-groups
  • first/last
  • date calculations
  • retain-statement
  • output-statement

The following solution needs further testing before i would call it stable.

 

Spoiler
data work.want;
   if eof = 0 then do;
      set work.Mediciene(firstobs= 2 keep= Drug_dt rename=(Drug_dt=NextStart)) end=eof;
   end;

   set work.Mediciene;
   by ptid Drug notsorted;

   length StartDate EndDate 8 outmarker 8;
   format StartDate EndDate date9.;
   retain StartDate;

   EndDate = drug_dt + dayssup -1;

   if first.Drug then do;
      NextStart = EndDate;
      StartDate = drug_dt;
   end; 

   Gap = NextStart - EndDate;
   outmarker = 0;

   if lowcase(Drug) = 'dexamethasone' then do;
      if Gap > 90 then do;
         outmarker = 1;
      end;
   end;
   else do;
      if Gap > 60 then do;
         outmarker = 2;
      end;
   end;

   if last.drug then do;
      outmarker = 3;
   end;


   if outmarker > 0 then do;
      output;
      StartDate = NextStart;
   end;

   drop outmarker Drug_dt Gap;
run;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 153 views
  • 1 like
  • 5 in conversation