I have the following data in the table:
ID | Rx Dt | Next Rx Dt | Days Supply | Base date for Persistence |
123 | 1/31/2013 | 3/5/2013 | 30 | 4/1/2013 |
123 | 3/5/2013 | 4/3/2013 | 30 | 5/4/2013 |
123 | 4/3/2013 | 5/1/2013 | 30 | 6/2/2013 |
123 | 5/1/2013 | 6/5/2013 | 30 | 6/30/2013 |
123 | 6/5/2013 | 7/3/2013 | 30 | 8/4/2013 |
123 | 7/3/2013 | 8/2/2013 | 30 | 9/1/2013 |
123 | 8/2/2013 | 1/28/2014 | 30 | 10/1/2013 |
123 | 1/28/2014 | 2/26/2014 | 30 | 3/29/2014 |
123 | 2/26/2014 | 3/26/2014 | 30 | 4/27/2014 |
123 | 3/26/2014 | 4/25/2014 | 30 | 5/25/2014 |
123 | 4/25/2014 | 6/25/2014 | 30 | 6/24/2014 |
123 | 6/25/2014 | 7/22/2014 | 30 | 8/24/2014 |
123 | 7/22/2014 | 10/25/2014 | 30 | 9/20/2014 |
What I have to do is check if Next Rx dt < Base date for Persistence. If it is TRUE then count as Therapy = 1 and Days on Therapy = 30. If the next record is TRUE again then Therapy = 1 and Days on Therapy = 60 and so on until I find a record where Next Rx dt < Base date for Persistence = FALSE. Then Therapy = . and Days on Therapy = . If for the following record, Next Rx dt < Base date for Persistence = TRUE then it will have Therapy =2 and Days of Supply =30. Next Record if it is TRUE then Therapy = 2 and Days of Supply = 60 and so on... After that I want Persitent Flag = 'Y' for each last record of the Therapy.
My Output should look like below:
ID | Rx Dt | Next Rx Dt | Days Supply | Base date for Persistence | Is Next Rx date < Base date | Therapy | Days on Therapy | Persitent Flag |
123 | 1/31/2013 | 3/5/2013 | 30 | 4/1/2013 | TRUE | 1 | 30 | |
123 | 3/5/2013 | 4/3/2013 | 30 | 5/4/2013 | TRUE | 1 | 60 | |
123 | 4/3/2013 | 5/1/2013 | 30 | 6/2/2013 | TRUE | 1 | 90 | |
123 | 5/1/2013 | 6/5/2013 | 30 | 6/30/2013 | TRUE | 1 | 120 | |
123 | 6/5/2013 | 7/3/2013 | 30 | 8/4/2013 | TRUE | 1 | 150 | |
123 | 7/3/2013 | 8/2/2013 | 30 | 9/1/2013 | TRUE | 1 | 180 | Y |
123 | 8/2/2013 | 1/28/2014 | 30 | 10/1/2013 | FALSE | |||
123 | 1/28/2014 | 2/26/2014 | 30 | 3/29/2014 | TRUE | 2 | 30 | |
123 | 2/26/2014 | 3/26/2014 | 30 | 4/27/2014 | TRUE | 2 | 60 | |
123 | 3/26/2014 | 4/25/2014 | 30 | 5/25/2014 | TRUE | 2 | 90 | Y |
123 | 4/25/2014 | 6/25/2014 | 30 | 6/24/2014 | FALSE | |||
123 | 6/25/2014 | 7/22/2014 | 30 | 8/24/2014 | TRUE | 3 | 30 | Y |
123 | 7/22/2014 | 10/25/2014 | 30 | 9/20/2014 | FALSE |
I would appreciate any help in this.
Thanks
Any help please...
Little changes to my earlier post. I do not need Persistent Flag. I need cummulative days between the Refill if the Patient is on Therapy. Below is the data I have :
ID | Rx Dt | Next Rx Dt | Days Supply | Days between next refill | Base date for Persistence |
123 | 1/31/2013 | 3/5/2013 | 30 | 33 | 4/1/2013 |
123 | 3/5/2013 | 4/3/2013 | 30 | 29 | 5/4/2013 |
123 | 4/3/2013 | 5/1/2013 | 30 | 28 | 6/2/2013 |
123 | 5/1/2013 | 6/5/2013 | 30 | 35 | 6/30/2013 |
123 | 6/5/2013 | 7/3/2013 | 30 | 28 | 8/4/2013 |
123 | 7/3/2013 | 8/2/2013 | 30 | 30 | 9/1/2013 |
123 | 8/2/2013 | 1/28/2014 | 30 | 10/1/2013 | |
123 | 1/28/2014 | 2/26/2014 | 30 | 29 | 3/29/2014 |
123 | 2/26/2014 | 3/26/2014 | 30 | 28 | 4/27/2014 |
123 | 3/26/2014 | 4/25/2014 | 30 | 30 | 5/25/2014 |
123 | 4/25/2014 | 6/25/2014 | 30 | 6/24/2014 | |
123 | 6/25/2014 | 7/22/2014 | 30 | 27 | 8/24/2014 |
123 | 7/22/2014 | 10/25/2014 | 30 | 9/20/2014 |
My Final Output looks like below:
ID | Rx Dt | Next Rx Dt | Days Supply | Days between next refill | Base date for Persistence | Is Next Rx date < Base date | Therapy | Days on Therapy(Cumm.) |
123 | 1/31/2013 | 3/5/2013 | 30 | 33 | 4/1/2013 | TRUE | 1 | 33 |
123 | 3/5/2013 | 4/3/2013 | 30 | 29 | 5/4/2013 | TRUE | 1 | 62 |
123 | 4/3/2013 | 5/1/2013 | 30 | 28 | 6/2/2013 | TRUE | 1 | 90 |
123 | 5/1/2013 | 6/5/2013 | 30 | 35 | 6/30/2013 | TRUE | 1 | 125 |
123 | 6/5/2013 | 7/3/2013 | 30 | 28 | 8/4/2013 | TRUE | 1 | 153 |
123 | 7/3/2013 | 8/2/2013 | 30 | 30 | 9/1/2013 | TRUE | 1 | 183 |
123 | 8/2/2013 | 1/28/2014 | 30 | 10/1/2013 | FALSE | |||
123 | 1/28/2014 | 2/26/2014 | 30 | 29 | 3/29/2014 | TRUE | 2 | 29 |
123 | 2/26/2014 | 3/26/2014 | 30 | 28 | 4/27/2014 | TRUE | 2 | 57 |
123 | 3/26/2014 | 4/25/2014 | 30 | 30 | 5/25/2014 | TRUE | 2 | 87 |
123 | 4/25/2014 | 6/25/2014 | 30 | 6/24/2014 | FALSE | |||
123 | 6/25/2014 | 7/22/2014 | 30 | 27 | 8/24/2014 | TRUE | 3 | 27 |
123 | 7/22/2014 | 10/25/2014 | 30 | 9/20/2014 | FALSE |
NOTE: Cumm. Days on Therapy is nothing but Cumm. Days between each refill for each Therapy
If the Patient is on Therapy first time then Therapy = 1. It will continue to be 1 until Is Next Rx date < Base date = FALSE. During that time you should do the cummulative Days on Therapy. When you see the next Is Next Rx date < Base date = TRUE then Therapy becomes 2 and .Start doing cummulative Days on Therapy from that point on until Is Next Rx date < Base date = FALSE. After that if you see Is Next Rx date < Base date = TRUE then Therapy = 3 and start doing Cumm. days on Therapy and so on
I would appreciate any help in this.
Thanks
data have; input ID RxDt : mmddyy10. NextRxDt : mmddyy10. DaysSupply Daysbetweennextrefill BasedateforPersistence : mmddyy10.; format RxDt NextRxDt BasedateforPersistence mmddyy10.; cards; 123 1/31/2013 3/5/2013 30 33 4/1/2013 123 3/5/2013 4/3/2013 30 29 5/4/2013 123 4/3/2013 5/1/2013 30 28 6/2/2013 123 5/1/2013 6/5/2013 30 35 6/30/2013 123 6/5/2013 7/3/2013 30 28 8/4/2013 123 7/3/2013 8/2/2013 30 30 9/1/2013 123 8/2/2013 1/28/2014 30 . 10/1/2013 123 1/28/2014 2/26/2014 30 29 3/29/2014 123 2/26/2014 3/26/2014 30 28 4/27/2014 123 3/26/2014 4/25/2014 30 30 5/25/2014 123 4/25/2014 6/25/2014 30 . 6/24/2014 123 6/25/2014 7/22/2014 30 27 8/24/2014 123 7/22/2014 10/25/2014 30 . 9/20/2014 ; run; data have; set have; length flag $ 10; if NextRxDt lt BasedateforPersistence then flag='TRUE'; else flag='FALSE'; run; data want; set have; by id flag notsorted; retain Therapy ; if first.id then do;n=.;Therapy=.;DaysonTherapy=.;end; if first.flag then do; if flag='TRUE' then do;n+1;Therapy=n;end; else Therapy=.; end; DaysonTherapy+Daysbetweennextrefill; if flag='FALSE' then DaysonTherapy=.; drop n; run;
Xia Keshan
Thanks Xia. It worked as wanted.. One question if I have one patient on 2 drugs. So there will be another field called drug. I want to sort them by Drug and then Patient ID and then do the # of Therapies and DaysonTherapy separately for each drug for each Patient.
How do change the logic above.
Thanks
I am in financial field , not in Pharmacy field . So hard to catch on you .
As my thought, if there was one more group variable DRUG :
data have;
set have;
length flag $ 10;
if NextRxDt lt BasedateforPersistence then flag='TRUE';
else flag='FALSE';
run;
proc sort data=have; by drug id;run;
data want;
set have;
by drug id flag notsorted;
retain Therapy ;
if first.id then do;n=.;Therapy=.;DaysonTherapy=.;end;
if first.flag then do;
if flag='TRUE' then do;n+1;Therapy=n;end;
else Therapy=.;
end;
DaysonTherapy+Daysbetweennextrefill;
if flag='FALSE' then DaysonTherapy=.;
drop n;
run;
Thanks Xia. I think I can use your code by putting into a macro and use it for the the other drugs. There are hardly 3 more drugs.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.