DATA Step, Macro, Functions and more

Consecutive Days calculation (Pharmacy claims)

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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,626

Re: Consecutive Days calculation (Pharmacy claims)

if you  want  "two days apart", change

 

dif(date) ne 1

into 

dif(date) > 2

 

View solution in original post


All Replies
Regular Contributor
Posts: 153

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,626

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,626

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 110 views
  • 0 likes
  • 3 in conversation