topic Re: Determining days cover and if a member swicth to a new therapy or if it is just and additional a in SAS Procedures

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

ssitharath0420 — Wed, 25 Jan 2017 20:35:39 GMT

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

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

mkeintz — Wed, 25 Jan 2017 20:39:24 GMT

And how about showing a table of expected output?

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

ballardw — Thu, 26 Jan 2017 22:02:10 GMT

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-data-AKA-generate/ta-p/258712 to generate datastep code from a SAS data set. That will completely describe your data and provide data that we can test code against.

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

ssitharath0420 — Wed, 25 Jan 2017 22:37:35 GMT

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.

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

ssitharath0420 — Wed, 25 Jan 2017 22:40:53 GMT

The output table should be:

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

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

ballardw — Thu, 26 Jan 2017 22:03:51 GMT

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.

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

ssitharath0420 — Thu, 26 Jan 2017 18:28:02 GMT

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

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

ssitharath0420 — Thu, 26 Jan 2017 18:30:11 GMT

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

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

HB — Thu, 26 Jan 2017 23:15:52 GMT

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.

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

mkeintz — Thu, 26 Jan 2017 22:58:26 GMT

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.

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

mkeintz — Thu, 26 Jan 2017 23:08:28 GMT

I doubt if this is the intended result

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

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

HB — Thu, 26 Jan 2017 23:20:18 GMT

mkeintz writes:

I doubt if this is the intended result

:

It includes dates in 2017 (OP asked for "within a year (2016)").

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.

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

ssitharath0420 — Fri, 27 Jan 2017 02:52:05 GMT

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

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

ssitharath0420 — Fri, 27 Jan 2017 02:54:01 GMT

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

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

ssitharath0420 — Fri, 27 Jan 2017 02:56:55 GMT

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.

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

mkeintz — Fri, 27 Jan 2017 04:16:24 GMT

@ssitharath0420

I asked why you have 355 (not 335). Your answer now raises another question. You chose to ignore Dec 31,2016 because it's the 366th day of the year, thereby getting 334 total days for 1A. Does that mean someone with coverage from Jan 1-Dec 30 has 1 day less than Jan 2-Dec 31?

Anyhow, assuming that's what you mean, then this should work:

data want (drop=n j d first_n cutdate mindate overlap);

  /* Read all recs for a patid, to determine which rec has earliest SERV_DATE1 */
  do N=1 by 1 until (last.patid);
    set testing;
    by patid;
    if serv_date1<mindate or N=1 then first_N=N;
    mindate=min(mindate,serv_date1);
  end;

  cutdate="01jan2016"d + 365; /* Earliest day to NOT include */
  array sd {*} serv_date: ;
  array ds {*} days_supply: ;

  /* Reread all N recs for a patid, and calc coverage days*/
  do J=1 to N;
    set testing;
    days=0;

    length class $13;
    if  N=1 then class='MONO';
    else if J=first_n then class='First Therapy';
    else class='Add On';

    do d=1 to dim(sd) while (ds{d}^=.);
      if d>1 then overlap = max(0,sd{d-1}+ds{d-1}+1-sd{d});
      else overlap=0;
      days=days+min(cutdate-sd{d},ds{d})-overlap;
    end;
    output;
  end;
run;

If this is a solution to your original task, then it shows the value and time savings of a clearly stated problem accompanied by an input example and a correct output example.

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

ssitharath0420 — Fri, 27 Jan 2017 05:15:10 GMT

What about Patient Id 5. This person switch to a new therapy. Patio 5 took therapy A from 01/01/2016 to 04/01/2016 and switch to therapy B from 05/01/2016 to 12/01/2016.

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

ssitharath0420 — Fri, 27 Jan 2017 05:15:12 GMT

What about Patient Id 5. This person switch to a new therapy. Patio 5 took therapy A from 01/01/2016 to 04/01/2016 and switch to therapy B from 05/01/2016 to 12/01/2016.

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

mkeintz — Fri, 27 Jan 2017 11:43:43 GMT

If therapy A ends on day X, and B begins the very next day, is that a switch? Or must there be uncovered days between the two therapies?

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

mkeintz — Fri, 27 Jan 2017 12:33:18 GMT

This solution uses proc sort, which I usually like to avoid because the result file does not have the same record order as the source data (often unwanted) and sorting large files can be expensive. However:


proc sort data=testing out=need;
  by patid serv_date1;
run;

data want (drop=d cutdate overlap end_date);
  set need;
  by patid;

  cutdate="01jan2016"d + 365; /* Earliest day to NOT include */
  array sd {*} serv_date: ;
  array ds {*} days_supply: ;

  days=0;
  do d=1 to dim(sd) while (ds{d}^=.);
    if d>1 then overlap = max(0,sd{d-1}+ds{d-1}+1-sd{d});
    else overlap=0;
    days = days + min(cutdate-sd{d},ds{d})-overlap;
  end;
  end_date = serv_date1 + days -1;

  length class $13; 
  class =ifc(serv_date1>lag(end_date),'SWITCH','ADD ON'); 
  if first.patid=1 and last.patid=1 then class='MONO'; 
  else if first.patid then class='First Therapy'; 
run;