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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.