topic Re: Consecutive Days calculation (Pharmacy claims) in SAS Programming

Consecutive Days calculation (Pharmacy claims)

mzahid — Tue, 06 Mar 2018 11:58:54 GMT

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

Re: Consecutive Days calculation (Pharmacy claims)

Oligolas — Tue, 06 Mar 2018 12:47:17 GMT

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

Re: Consecutive Days calculation (Pharmacy claims)

Ksharp — Tue, 06 Mar 2018 12:54:50 GMT

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;

Re: Consecutive Days calculation (Pharmacy claims)

mzahid — Tue, 06 Mar 2018 20:20:55 GMT

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

Re: Consecutive Days calculation (Pharmacy claims)

Ksharp — Wed, 07 Mar 2018 10:23:20 GMT

if you want "two days apart", change

dif(date) ne 1

into

dif(date) > 2