Solved
New Contributor
Posts: 2

# Consecutive Days calculation (Pharmacy claims)

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

Accepted Solutions
Solution
‎03-07-2018 09:11 AM
Super User
Posts: 10,849

## Re: Consecutive Days calculation (Pharmacy claims)

if you  want  "two days apart", change

``dif(date) ne 1``

into

``dif(date) > 2``

All Replies
Regular Contributor
Posts: 164

## Re: Consecutive Days calculation (Pharmacy claims)

[ Edited ]

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

________________________

- Cheers -

Super User
Posts: 10,849

## Re: Consecutive Days calculation (Pharmacy claims)

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;``````
New Contributor
Posts: 2

## Re: Consecutive Days calculation (Pharmacy claims)

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

Solution
‎03-07-2018 09:11 AM
Super User
Posts: 10,849

## Re: Consecutive Days calculation (Pharmacy claims)

if you  want  "two days apart", change

``dif(date) ne 1``

into

``dif(date) > 2``

☑ This topic is solved.