topic Re: How to create a report by assigning Start and End Date in SAS Programming

How to create a report by assigning Start and End Date

umeshgiri48 — Mon, 16 Jul 2018 14:56:32 GMT

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

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

RW9 — Thu, 05 Jul 2018 15:24:22 GMT

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.

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

umeshgiri48 — Mon, 09 Jul 2018 15:08:15 GMT

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?

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

error_prone — Mon, 09 Jul 2018 17:07:36 GMT

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

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

umeshgiri48 — Sat, 14 Jul 2018 09:03:12 GMT

Hi,

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

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

PaigeMiller — Sat, 14 Jul 2018 11:03:15 GMT

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.

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

umeshgiri48 — Mon, 16 Jul 2018 14:59:30 GMT

Hi,

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

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

PaigeMiller — Mon, 16 Jul 2018 17:05:35 GMT

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;

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

andreas_lds — Mon, 16 Jul 2018 15:55:20 GMT

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.

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;