Determining days cover and if a member swicth to a new therapy or if it is just and additional addon

[ssitharath0420]

Hello,

I am trying to determine the best procedure to determine the days covered within a year (2016) for each therapy.  I also need to determine whether the therapy is a addition addon by the prescriber or whether they switch to a new therapy.

Output needed:

Patid,Therapy,Daycovered (take intoconsideration of overlapping), Class (Mono Addon Switch)

Any helps would be appreciated!

This is the data:

PATID	Therapy	serv_date1	serv_date2	serv_date3	serv_date4	serv_date5	days_supply1	days_supply2	days_supply3	days_supply4	days_supply5
1	A	1/11/2016	4/13/2016	7/18/2016	10/28/2016		90	90	90	90
1	B	6/18/2016	9/22/2016	12/13/2016	12/28/2016		90	90	30	30
2	A	1/11/2016	4/18/2016	7/14/2016	10/13/2016		90	90	90	90
2	B	1/11/2016	4/16/2016	7/18/2016	10/13/2016	11/22/2016	90	90	90	90	90
3	A	3/2/2016	8/29/2016	11/5/2016			90	90	90
3	B	2/5/2016	6/28/2016	9/6/2016	12/2/2016		90	90	90	90
3	C	3/26/2016	6/27/2016	9/6/2016	11/29/2016		90	90	90	90
4	A	1/1/2016	4/1/2016	4/13/2016	12/31/2016		90	90	90	30

[mkeintz]

And how about showing a table of expected output?

[ssitharath0420]

The output table should be:

Patid, therapy,dayscovered, class (mono,addon,switch)

[ssitharath0420]

Hi,

Please find the sample output

PATID	Therapy	Days_Covered	Class
1	A	355	First Therapy
1	B	190	AddOn
2	A	347	First Therapy
2	B	346	AddOn
3	A	214	AddOn
3	B	276	First Therapy
3	C	277	AddOn
4	A	180	Mono
5	A	120	First Therapy
5	B	120	Switched

[ballardw]

Are your date variables SAS date values or character variables?

It will also help if you define "days covered" in terms of the variables presented. I might make a guess and sometimes be right but you might have rules that are not obvious.

It really helps to provide data in the form of a data step. You can follow instruction here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to generate datastep code from a SAS data set. That will completely describe your data and provide data that we can test code against.

[ssitharath0420]

I am downloaded the macro but can't get it to create a data step.  The date is mmddyy10. format.  DayCovered is how many days are covered for these therapy within 365 days for 2016.

[ballardw]

Walking through how to define something interms of variables:

You know what 'days covered' means. I see 5 date variables and 5 variables called "days_supply".

Is the "days covered" associateed with PatId=1 ant Therapy='A' the number of days between serv_date1 and servdate2? Or does it have some relationship to days_supply1? Or are we supposed to "know" that 1/11/2016 to 4/10/2016 are "covered" because of the days_supply?  And since you have serv_date2 of 4/13/2016 how does the gap get considered? Are we supposed to calculate consecutive days or total days within a calendar year.

Note that having the "days_supply" value EXACTLY the same for every record means it is pretty much not needed. If it varies then you need to show some examples.

Are we supposed to only consider one therapy at a time or are you looking to summarize across multiple therapies?

And how do you want "take into consideration of overlapping"? Set a report flag that a period overlapped? Indicate how many days overlapped?

You should show what a hand calculated result should look like for at least a couple of cases. Including one the has varying "days_supply" values.

You  say the output table should have:

The output table should be:

Patid, therapy,dayscovered, class (mono,addon,switch). Without knowing whether the output should only have one record per patient or not, it is kind of difficult to know where we are placing a "class" variable. That is why we asked for an example of the output, not a list of the variables.

[ssitharath0420]

Hi.  Please find the attached datastep for the sample data.

data WORK.TESTING;
  infile datalines dsd truncover;
  input PATID:32. Therapy:$1. serv_date1:DATE9. serv_date2:DATE9. serv_date3:DATE9. serv_date4:DATE9. serv_date5:DATE9. days_supply1:32. days_supply2:32. days_supply3:32. days_supply4:32. days_supply5:32.;
datalines4;
1,A,11JAN2016,13APR2016,18JUL2016,28OCT2016,,90,90,90,90,
1,B,18JUN2016,22SEP2016,13DEC2016,28DEC2016,,90,90,30,30,
2,A,11JAN2016,18APR2016,14JUL2016,13OCT2016,,90,90,90,90,
2,B,11JAN2016,16APR2016,18JUL2016,13OCT2016,22NOV2016,90,90,90,90,90
3,A,02MAR2016,29AUG2016,05NOV2016,,,90,90,90,,
3,B,05FEB2016,28JUN2016,06SEP2016,02DEC2016,,90,90,90,90,
3,C,26MAR2016,27JUN2016,06SEP2016,29NOV2016,,90,90,90,90,
4,A,01JAN2016,01APR2016,13APR2016,31DEC2016,,90,90,90,30,
5,A,01JAN2016,01FEB2016,01MAR2016,01APR2016,,30,30,30,30,
5,B,01MAY2016,01JUN2016,01JUL2016,01AUG2016,,30,30,30,30,
;;;;

Below is the needed Output:

PATID	Therapy	Days_Covered	Class
1	A	355	First Therapy
1	B	190	AddOn
2	A	347	First Therapy
2	B	346	AddOn
3	A	214	AddOn
3	B	276	First Therapy
3	C	277	AddOn
4	A	180	Mono
5	A	120	First Therapy
5	B	120	Switched

[HB]

data WORK.TESTING;
	infile datalines dsd truncover;
	input PATID:32. Therapy:$1. serv_date1:DATE9. serv_date2:DATE9. serv_date3:DATE9. 
	serv_date4:DATE9. serv_date5:DATE9. days_supply1:32. days_supply2:32. 
	days_supply3:32. days_supply4:32. days_supply5:32.;
datalines4;
1,A,11JAN2016,13APR2016,18JUL2016,28OCT2016,,90,90,90,90,
1,B,18JUN2016,22SEP2016,13DEC2016,28DEC2016,,90,90,30,30,
2,A,11JAN2016,18APR2016,14JUL2016,13OCT2016,,90,90,90,90,
2,B,11JAN2016,16APR2016,18JUL2016,13OCT2016,22NOV2016,90,90,90,90,90
3,A,02MAR2016,29AUG2016,05NOV2016,,,90,90,90,,
3,B,05FEB2016,28JUN2016,06SEP2016,02DEC2016,,90,90,90,90,
3,C,26MAR2016,27JUN2016,06SEP2016,29NOV2016,,90,90,90,90,
4,A,01JAN2016,01APR2016,13APR2016,31DEC2016,,90,90,90,30,
5,A,01JAN2016,01FEB2016,01MAR2016,01APR2016,,30,30,30,30,
5,B,01MAY2016,01JUN2016,01JUL2016,01AUG2016,,30,30,30,30,
;;;;
run;

*assume days_covered is a simple last therapy date minus first therapy date 
 since OP is unclear and provides no instructions for calculating days_covered
 also use a brute force method assuming only 5 service dates since OP provides 
 no inoformation regarding how many possible service dates there might be;
data testing;
	set testing;
	days_covered = 0;
	if serv_date2 then days_covered = serv_date2 - serv_date1;
	if serv_date3 then days_covered = serv_date3 - serv_date1;
	if serv_date4 then days_covered = serv_date4 - serv_date1;
	if serv_date5 then days_covered = serv_date5 - serv_date1;
run;

proc sort data = testing;
	by patid descending days_covered ;
run;

data testing;
	set testing;
	by patid;
	class = 'Unknown         ';
	if first.patid then class = 'First Therapy';
		else class = 'Add on';
	if first.patid and last.patid then class = 'Mono';
	keep patid therapy days_covered class;
run;
 

Output set is:

PATID	Therapy	days_covered	class
1	A	291	First Therapy
1	B	193	Add on
2	B	316	First Therapy
2	A	276	Add on
3	B	301	First Therapy
3	A	248	Add on
3	C	248	Add on
4	A	365	Mono
5	B	92	First Therapy
5	A	91	Add on

Edit:

You may need to sort by serv_date1 as well

      by patid serv_date1 descending days_covered ;

This would be to cover situations where a therapy which had a larger days_covered number but started after a different therapy isn't classed as first therapy.  Assumes the therapy with the earliest serv_date regardless of length is the first therapy, and one that started after, even if it ran longer, is an add on.  

[mkeintz]

I doubt if this is the intended result

:

1. It includes dates in 2017  (OP asked for "within a year (2016)").
   
   
2. It double counts days in 2016 in which therapies overlap  (after all  there is the OP's ambiguous request to "take into consideration overlapping"). 

[HB]

mkeintz writes:

---

I doubt if this is the intended result

:

1. It includes dates in 2017  (OP asked for "within a year (2016)").
   
   
2. It double counts days in 2016 in which therapies overlap  (after all  there is the OP's ambiguous request to "take into consideration overlapping"). 

---

You may very well be correct.  Hard to say based on the info provided.

[ssitharath0420]

Yes, my timeframe is from 01/01/2016 to 12/31/2016.  So patient can potenially have a filled date up to 12/31/2016.

An example of how days covered should be calculated for 1A:

start	Go to date	DayCovered
1/11/2016	4/10/2016	90
4/13/2016	7/12/2016	90
7/18/2016	10/16/2016	90
10/28/2016	12/31/2016	64
	Days Covered	334

[ssitharath0420]

Patient 5 should be classify as "Switch"  because they took Therapy A from 01/01/2016 to 04/01/2016.  Then they completely stop it and resume again with a new therapy B from 05/01/2016 and on.

[mkeintz]

Please  tell us how you got 355 for the 1A record.  You will then likely have a well-defined problem for which a well-constructed solution can probably come back in reasonable time.

[ssitharath0420]

I am sorry.  It should 334.  The 335 comes from the first filled 01/01/2016 and subtracting that from 12/31/2016.

This is how days supply should be calculated.  An example of Patient ID 1 Therapy A:

start	Go to date	DayCovered
1/11/2016	4/10/2016	90
4/13/2016	7/12/2016	90
7/18/2016	10/16/2016	90
10/28/2016	12/31/2016	64
	Days Covered	334