Help with SAS algorithm to calculate end date and persistence

[JME1]

Hello!

I needed help for a SAS code to measure the number of days patients were on a drug (for initial and refill prescriptions) until there is a gap >30 days or discontinuation. 

I also want to create a new variable 'end date' defined as the date the prescribed supply of pills would be completed if all pills were taken.

My data set looks like this:

enrolid      Prescription date          Days of Supply        end_Date         Tot1  Tot2     

1                11/11/2015                      30

1                12/04/2015                      30

2                03/10/2015                      90

2                06/23/2015                      90 

2                 09/03/2015                     90

3                 01/14/2015                     30

3                 04/25/2015                     30

• For example, a person with enrolid 1 would have an end_date of 01/03/2016 (12/04/2015+30).
• For the number of days on drug I would want to create a new variable that measures the total days of supply it in two ways: (Tot1) - One which would sum up the two days of supplies 30+30, and another  (Tot2) that would be 23+30 since there isn't a 30-day gap between the initial prescription date and the refill date. 

However, for enrolid 3 - since between their initial and refill prescription there is more than a 30-day gap, the end date would be '02/13/2015' (which is the date of initial prescription + 30 days of supply) and total days would be 30.

Thank you!

I know this a little confusing but I'd appreciate any assistance

[jimbarbour]

Would you want results like the below?  Code is below that.  If this isn't what you want, can you post for each record what the expected date should be?

Jim

DATA	Rx_Supply;

	INPUT	EnrolID
			Prescription_Date	:	ANYDTDTE10.
			Days_of_Supply
			;

	FORMAT	Prescription_Date	MMDDYYS10.;
DATALINES;
1                11/11/2015                      30
1                12/04/2015                      30
2                03/10/2015                      90
2                06/23/2015                      90 
2                 09/03/2015                     90
3                 01/14/2015                     30
3                 04/25/2015                     30
;
RUN;

**	Get_Var_By_Pointer is used to read ahead and get info from the next record	**;
%MACRO	Get_Var_By_Pointer(d, x, y, j);
	_p_					=	_n_	+	&j;

	IF	(1 		<= _p_ 	<=	_o_)	THEN
		DO;
			_Found		=	1;
			SET	&d(KEEP=&x 	RENAME=(&x=&y))	POINT=_p_	NOBS=_o_;
		END;
	ELSE
		DO;
			_Found		=	0;
		END;
%MEND	Get_Var_By_Pointer;

DATA	Rx_Evaluated;
	DROP	_:;

	SET	Rx_Supply;

	FORMAT	End_Date	MMDDYYS10.;

	_Current_Period				=	INTNX('DAY', Prescription_Date, Days_of_Supply);

	**	Get the EnrolID from the next record.	**;
	%Get_Var_By_Pointer(Rx_Supply, EnrolID, _Next_EnrolID, +1);

	IF	_Next_EnrolID			=	EnrolID			THEN
		DO;
			**	Get the Prescription_Date from the next record.	**;
			%Get_Var_By_Pointer(Rx_Supply, Prescription_Date, 	_Next_Date, +1);
			**	Get the Days_of_Supply from the next record.	**;
			%Get_Var_By_Pointer(Rx_Supply, Days_of_Supply, 		_Next_Days, +1);
			IF	_Next_Date		<=	_Current_Period	THEN
				DO;
					End_Date	=	INTNX('DAY', _Next_Date, _Next_Days);
				END;
			ELSE
				DO;
					End_Date	=	_Current_Period;
				END;
		END;
	ELSE
		DO;
			End_Date			=	_Current_Period;
		END;
RUN;

[JME1]

Thank you for getting back to me! I tried this method and although it's really close, the image attached below has the expected end dates for each enrolid. 

For enrolid 2: The time from the initial prescription (3/10/2015 + 90 days of supply)  would have been 6/08/2015, but since they had another prescription on 6/23/2015 which is less than 30 days from when they should have finished their regimen, the end_date would be 12/2/2015 for all the records. 

For enrolid 3: The first record shows that they would have completed their drug regimen by 2/13/2015. However, they got a refill prescription >30 days from 2/13/2015 - therefore I'm not interested in the end date for this particular refill, and their end date remains 2/13/2015.

I'm sorry if it's a little unclear,  but I'm happy to try to better explain it. 

Thank you!

[jimbarbour]

OK, I think I get it.

There's a current period which is the Prescription_Date + Days_of_Supply, but there is also a grace period which is the current period + 30.  As long as the next refill is within the grace period, then the individual has an active "trial."  If the next refill falls outside the grace period, then the trial is "dead," and we compute no further end dates.

The program would be modified as shown below and would give us the results shown.  For test purposes, I added record four which shows that the individual continued getting refills all the way through the end of the year (2016 in this case) and that, indeed, the program computes the end_date correctly (as I understand it) and back populates that end date on all records.  

I also added two columns, Current_Period and Grace_Period to help me QC the results.  You can drop them easily if you like by renaming them to _Current_Period and _Grace_Period.  The program automatically drops all variables prefixed with an underscore.

Jim

Results:

Program:

**	Get_Var_By_Pointer is used to read ahead and get info from the next record	**;
%MACRO	Get_Var_By_Pointer(d, x, y, j);
	_p_									=	_n_		+		&j;

	IF	(1 				<= _p_ 			<=	_o_)			THEN
		DO;
			_Found						=	1;
			SET	&d(KEEP=&x 	RENAME=(&x=&y))		POINT=_p_	NOBS=_o_;
		END;
	ELSE
		DO;
			_Found						=	0;
		END;
%MEND	Get_Var_By_Pointer;

*-------------------------------------------------------------------------------**;

DATA	Rx_Evaluated;
	DROP	_:;

	SET	Rx_Supply;
		BY	EnrolID	NOTSORTED;

	FORMAT	Current_Period	MMDDYYS10.;
	FORMAT	Grace_Period	MMDDYYS10.;
	FORMAT	End_Date		MMDDYYS10.;
	FORMAT	_Work_Date		MMDDYYS10.;

	RETAIN	_Dead_Trial;
	RETAIN	End_Date;

	Current_Period						=	INTNX('DAY', Prescription_Date, Days_of_Supply);
	Grace_Period						=	Current_Period	+	30;

	IF	FIRST.EnrolID											THEN
		DO;
			End_Date					=	Current_Period;
			_Dead_Trial					=	0;
		END;

	IF	NOT	_Dead_Trial											THEN
		DO;
			_Work_Date					=	Current_Period;
			_End_of_Period				=	0;
			_Read_Ahead					=	0;
			DO	UNTIL	(_End_of_Period);
				LINK	Read_Ahead;
			END;
		END;
	******;
	RETURN;
	******;

	**********;
	Read_Ahead:
	**********;
		_Read_Ahead						+	1;

		**	Get the EnrolID from the next record.	**;
		%Get_Var_By_Pointer(Rx_Supply, EnrolID, _Next_EnrolID, _Read_Ahead);

		IF	_FOUND												AND
			_Next_EnrolID				=	EnrolID				THEN
			DO;
				**	Get the Prescription_Date from the next record.	**;
				%Get_Var_By_Pointer(Rx_Supply, Prescription_Date, 	_Next_Date, _Read_Ahead);
				**	Get the Days_of_Supply from the next record.	**;
				%Get_Var_By_Pointer(Rx_Supply, Days_of_Supply, 		_Next_Days, _Read_Ahead);
				IF	_Next_Date			<=	(_Work_Date + 30)	THEN
					DO;
						End_Date		=	INTNX('DAY', _Next_Date, _Next_Days);
						_Work_Date		=	End_Date;
					END;
				ELSE
					DO;
						_Dead_Trial		=	1;
						_End_of_Period	=	1;
					END;
			END;
		ELSE
			DO;
				End_Date				=	_Work_Date;
				_End_of_Period			=	1;
			END;
	******;
	RETURN;
	******;
RUN;

[JME1]

Thank you! This is exactly what I needed. I'll attempt this code with my cohort and let you know if I run into any difficulty!

[jimbarbour]

Excellent.  I hope it works out.

Jim