## Help with Calculating Days on Therapy and # of Therapies

Solved
Frequent Contributor
Posts: 131

# Help with Calculating Days on Therapy and # of Therapies

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

Accepted Solutions
Solution
‎03-27-2015 04:04 PM
Posts: 3,167

## Re: Help with Calculating Days on Therapy and # of Therapies

Maybe the sooner you wrap up this thread:

the sooner you will get responses?

All Replies
Frequent Contributor
Posts: 131

## Re: Help with Calculating Days on Therapy and # of Therapies

Solution
‎03-27-2015 04:04 PM
Posts: 3,167

## Re: Help with Calculating Days on Therapy and # of Therapies

Maybe the sooner you wrap up this thread:

the sooner you will get responses?

Frequent Contributor
Posts: 131

## Re: Help with Calculating Days on Therapy and # of Therapies

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

Super User
Posts: 10,787

## Re: Help with Calculating Days on Therapy and # of Therapies

```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

Frequent Contributor
Posts: 131

## Re: Help with Calculating Days on Therapy and # of Therapies

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

Super User
Posts: 10,787

## Re: Help with Calculating Days on Therapy and # of Therapies

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;

Frequent Contributor
Posts: 131

## Re: Help with Calculating Days on Therapy and # of Therapies

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.

🔒 This topic is solved and locked.