Reconciliation of prescription dates

[skyvalley81]

Hi everyone,

I have a dataset containing patients, date of treatment and the drug that was prescribed.
When there are more than two different drugs on the same prescription date I need to check if one of the drugs was also the last drug on the previous prescription date
and place this product first and order the other same date by drugcode.

Could you please suggest me how I can approach this problem?

Thanks in advance for your help.

Below you can find the datasets:

data have;
input id:$3. dxdate:date9. treatment:$6. dupDrugFn:8.;
format dxdate date9. ;
cards;
100 09MAY2006 drug1 0
100 24OCT2006 drug1 0
100 19APR2007 drug1 0
100 17DEC2007 drug1 0
100 07JUL2008 drug1 0
100 13AUG2008 drug2 0
100 09JUN2009 drug2 1
100 09JUN2009 drug1 1
100 28OCT2009 drug2 1
100 28OCT2009 drug1 1
100 19MAR2010 drug3 1
100 19MAR2010 drug4 1
100 26MAR2010 drug3 1
100 26MAR2010 drug4 1
100 15DEC2010 drug3 1
100 15DEC2010 drug4 1
100 15NOV2011 drug3 1
100 15NOV2011 drug4 1
100 21MAY2012 drug3 1
100 21MAY2012 drug5 1
100 05FEB2013 drug6 0
100 14JUN2013 drug5 0
100 26JUL2013 drug7 0
100 02JAN2014 drug6 1
100 02JAN2014 drug5 1
100 02JAN2014 drug7 1
100 02FEB2016 drug8 1
100 02FEB2016 drug6 1
100 02FEB2016 drug5 1
;
run;

data have;
input id:$3. dxdate:date9. treatment:$6. dupDrugFn:8.;
format dxdate date9. ;
cards;
100 09MAY2006 drug1 0
100 24OCT2006 drug1 0
100 19APR2007 drug1 0
100 17DEC2007 drug1 0
100 07JUL2008 drug1 0
100 13AUG2008 drug2 0
100 09JUN2009 drug2 1
100 09JUN2009 drug1 1
100 28OCT2009 drug1 1
100 28OCT2009 drug2 1
100 19MAR2010 drug3 1
100 19MAR2010 drug4 1
100 26MAR2010 drug4 1
100 26MAR2010 drug3 1
100 15DEC2010 drug3 1
100 15DEC2010 drug4 1
100 15NOV2011 drug4 1
100 15NOV2011 drug3 1
100 21MAY2012 drug3 1
100 21MAY2012 drug5 1
100 05FEB2013 drug6 0
100 14JUN2013 drug5 0
100 26JUL2013 drug7 0
100 02JAN2014 drug7 1
100 02JAN2014 drug5 1
100 02JAN2014 drug6 1
100 02FEB2016 drug6 1
100 02FEB2016 drug5 1
100 02FEB2016 drug8 1
;
run;

[Reeza]

Can you highlight the differences between have and want and specify the logic?

EDIT: It looks like it's been sorted by date and then drug but I can't see any differences beyond that. 

If that's all you want use PROC SORT. 

proc sort data=have;
by id date drug;
run;

---

@skyvalley81 wrote:

Hi everyone,

I have a dataset containing patients, date of treatment and the drug that was prescribed.
When there are more than two different drugs on the same prescription date I need to check if one of the drugs was also the last drug on the previous prescription date
and place this product first and order the other same date by drugcode.

Could you please suggest me how I can approach this problem?

Thanks in advance for your help.

Below you can find the datasets:

data have;
input id:$3. dxdate:date9. treatment:$6. dupDrugFn:8.;
format dxdate date9. ;
cards;
100 09MAY2006 drug1 0
100 24OCT2006 drug1 0
100 19APR2007 drug1 0
100 17DEC2007 drug1 0
100 07JUL2008 drug1 0
100 13AUG2008 drug2 0
100 09JUN2009 drug2 1
100 09JUN2009 drug1 1
100 28OCT2009 drug2 1
100 28OCT2009 drug1 1
100 19MAR2010 drug3 1
100 19MAR2010 drug4 1
100 26MAR2010 drug3 1
100 26MAR2010 drug4 1
100 15DEC2010 drug3 1
100 15DEC2010 drug4 1
100 15NOV2011 drug3 1
100 15NOV2011 drug4 1
100 21MAY2012 drug3 1
100 21MAY2012 drug5 1
100 05FEB2013 drug6 0
100 14JUN2013 drug5 0
100 26JUL2013 drug7 0
100 02JAN2014 drug6 1
100 02JAN2014 drug5 1
100 02JAN2014 drug7 1
100 02FEB2016 drug8 1
100 02FEB2016 drug6 1
100 02FEB2016 drug5 1
;
run;

data have;
input id:$3. dxdate:date9. treatment:$6. dupDrugFn:8.;
format dxdate date9. ;
cards;
100 09MAY2006 drug1 0
100 24OCT2006 drug1 0
100 19APR2007 drug1 0
100 17DEC2007 drug1 0
100 07JUL2008 drug1 0
100 13AUG2008 drug2 0
100 09JUN2009 drug2 1
100 09JUN2009 drug1 1
100 28OCT2009 drug1 1
100 28OCT2009 drug2 1
100 19MAR2010 drug3 1
100 19MAR2010 drug4 1
100 26MAR2010 drug4 1
100 26MAR2010 drug3 1
100 15DEC2010 drug3 1
100 15DEC2010 drug4 1
100 15NOV2011 drug4 1
100 15NOV2011 drug3 1
100 21MAY2012 drug3 1
100 21MAY2012 drug5 1
100 05FEB2013 drug6 0
100 14JUN2013 drug5 0
100 26JUL2013 drug7 0
100 02JAN2014 drug7 1
100 02JAN2014 drug5 1
100 02JAN2014 drug6 1
100 02FEB2016 drug6 1
100 02FEB2016 drug5 1
100 02FEB2016 drug8 1
;
run;

---

[skyvalley81]

Hi Reeza,

Thank you very much for looking into this problem.

The logic here is the following:
1) We examine when there are 2 or more drugs on the same day dupDrugFn = 1. The first date that we have two different drugs is on line 7-8 (09JUN2009).
2) If any of those products is the same with the drug in the previous date (13AUG2008) then we bring this drug first. In this example, drug2 is already first so we leave it as it is.
3) Then we move to the next date 28-Oct-2009 (line 9-10). Since the last drug on 9-Jun-2009 is DRUG1 we reverse the order of drugs in lines 9-10 and bring first DRUG1 and then we put DRUG2.

The same logic applies to other rows.

I hope it makes more sense now.

[Reeza]

So you’re just changing the ordering. What’s the end goal/need for this?

[skyvalley81]

Hi Reeza,

Thank you very much for getting back.

That is right we reorder the drugs based on the drug on the last prescription date. I am involved in a research project and i would like to calculate persistence/adherence on some medications.

Do you have any idea how i can approach it?  

[PGStats]

If you are looking at drug use patterns, you could encode drug sequences as series of letters (ex. "AAABB" : the patient switched from drug A to drug B) and use regular expression pattern matching to look for patterns of interest ( pattern "A+B+" would match). Just an idea.

[Reeza]

---

@skyvalley81 wrote:

Hi Reeza,

Thank you very much for getting back.

That is right we reorder the drugs based on the drug on the last prescription date. I am involved in a research project and i would like to calculate persistence/adherence on some medications.

Do you have any idea how i can approach it?  

---

If that's the case, I would be sorting by drug - not date and then following that drug over time.  Because you're trying to see if a patient stayed with a specific drug for a specific duration?

[Ksharp · Accepted solution]

OK. How about this one ?

data have;
input id:$3. dxdate:date9. treatment:$6. dupDrugFn:8.;
format dxdate date9. ;
cards;
100 09MAY2006 drug1 0
100 24OCT2006 drug1 0
100 19APR2007 drug1 0
100 17DEC2007 drug1 0
100 07JUL2008 drug1 0
100 13AUG2008 drug2 0
100 09JUN2009 drug2 1
100 09JUN2009 drug1 1
100 28OCT2009 drug2 1
100 28OCT2009 drug1 1
100 19MAR2010 drug3 1
100 19MAR2010 drug4 1
100 26MAR2010 drug3 1
100 26MAR2010 drug4 1
100 15DEC2010 drug3 1
100 15DEC2010 drug4 1
100 15NOV2011 drug3 1
100 15NOV2011 drug4 1
100 21MAY2012 drug3 1
100 21MAY2012 drug5 1
100 05FEB2013 drug6 0
100 14JUN2013 drug5 0
100 26JUL2013 drug7 0
100 02JAN2014 drug6 1
100 02JAN2014 drug5 1
100 02JAN2014 drug7 1
100 02FEB2016 drug8 1
100 02FEB2016 drug6 1
100 02FEB2016 drug5 1
;
run;
data want;
if _n_=1 then do;
 if 0 then set have;
 declare hash h(ordered:'y');
 declare hiter hi('h');
 h.definekey('treatment');
 h.definedata('treatment');
 h.definedone();
end;
length last $ 40;
retain last;
do until(last.dxdate);
 set have;
 by id 	dxdate;
 h.ref();
end;
if h.find(key:last)=0 then do;
 output;
 h.remove(key:last);
end;
do while(hi.next()=0);
 output;
end;
last=treatment;
h.clear();
drop last;
run;

[skyvalley81]

Thank you very much all guys @Ksharp,  @PGStats, @Reeza for looking into this problem.

I will try to digest it and I will come back if I have any questions.

Again thank you very much @Ksharp for your elegant solution.