Consecutive Days calculation (Pharmacy claims)

[mzahid]

Hi,

I need help on creating a consecutive days value based on memberid, fill_date and days_supply_cnt variables for a pharmacy claims SAS 

dataset.  I'm trying to find out the consecutive streak of a drug use for each member for the last six months.  For example a 30 days drug 

supply on Jan 1st would give a member with memberid 12345 a value of 30 days.  If on Jan 15th, the same member gets another 

prescription for a 10 days, the value would still be 30 days.  But if another member gets one prescription on Jan 1st for 30 days and 

another prescription on Jan 25th for 10 days the days streak would now be at 35 days.  If there was a gap of more than 2 days between 

prescriptions  a new set of days would be created based on above algorithm. 

Please Note: This data is already limited to specific drug for our analysis,

See example below

MemberId .    Fill_date            Days_supply_cnt

12345             01/01/2017      30

12345             01/15/2017      10

12345             02/10/2017      60

23456             01/01/2017      30

23456 .           01/25/2017      10

23456             04/01/2017       90

Desired OutPut

MemberId .  Consec_days

12345         30

12345         60

23456         35

23456         90

I would appreciate any help on this.

Thanks

Zahid Khan

[Oligolas]

Hi,
the rules you follow are not clear to me:

"If on Jan 15th, the same member gets another prescription for a 10 days, the value would still be 30 days. But if another member gets one prescription on Jan 1st for 30 days and another prescription on Jan 25th for 10 days the days streak would now be at 35 days"

With the rows 30 and 10 you create one time a row of 30 and the other time a row of 35, what's the trigger? is it a date other than the 15th of the month? and what is the rule, is it 30+10/2?

"If there was a gap of more than 2 days between prescriptions a new set of days would be created based on above algorithm."

Please clarify and provide an example

[Ksharp]

Assuming I understood what you mean.

data have;
input MemberId     Fill_date : mmddyy12.   Days_supply_cnt;
format fill_date mmddyy10.;
cards;
12345             01/01/2017      30
12345             01/15/2017      10
12345             02/10/2017      60
23456             01/01/2017      30
23456           01/25/2017      10
23456             04/01/2017       90
;
run;

data temp;
 set have;
 do date=fill_date to fill_date+Days_supply_cnt-1;
   output;
 end;
keep memberid date;
format date mmddyy10.;
run;
proc sort data=temp out=temp1 nodupkey;
 by memberid date;
run;
data temp2;
 set temp1;
 by memberid;
 if first.memberid or dif(date) ne 1 then group+1;
run;
proc freq data=temp2 noprint;
table memberid*group/out=want list;
run;

[mzahid]

Thank you so much for your help.  I tested the code, it works for the most part.  There are some situations where group =1 would end two days before the group 2 starts.  In the below example Group 1 has a count of 3 and group 2 has a count of 5.  If I wanted to combine group 1 and group 2 so the total count would be 8, since they are only two days apart.  How would I go about it.  Again, thanks for your time. 
MemberId .     fill_date             Group123456 .            21oct2017 .       1123456.             22oct2017 .       1123456 .            23oct2017 .       1123456 .            25oct2017 .       2123456 .            26oct2017 .       2123456 .            27oct2017 .       2123456 .            28oct2017 .       2123456 .            29oct2017 .       2

[Ksharp · Accepted solution]

if you  want  "two days apart", change

dif(date) ne 1

into 

dif(date) > 2