BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
umeshgiri48
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

umeshgiri48
Obsidian | Level 7

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?

error_prone
Barite | Level 11

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

umeshgiri48
Obsidian | Level 7

Hi,

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

PaigeMiller
Diamond | Level 26

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
umeshgiri48
Obsidian | Level 7

Hi,

 

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

PaigeMiller
Diamond | Level 26

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
andreas_lds
Jade | Level 19

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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