Quartz | Level 8

## Calculate Days Missed in between days/shifting dates

I need some help and I hope I am able to explain this correctly. I would like to calculate the days a particular drug is missed per year by month.

For the examples that I have, The patient filled the drug early for some months and I would like to shift the actual dates so there are no negative missed days.

So for drug A, the total missed days should be 73 and for Drug B the total missed days should be 147

But  I am stumped on how to calculate this without the negative values.

Any assistance would be appreciated

 ID First Name Last Name Medication Fill Date Days Supply Next Refill Date Days In Beween 1234 John Doe Drug A 1/23/2021 90 1234 John Doe Drug A 4/1/2021 30 4/23/2021 -22 1234 John Doe Drug A 7/30/2021 30 5/1/2021 90 1234 John Doe Drug A 8/23/2021 30 8/29/2021 -6 1234 John Doe Drug A 9/23/2021 30 9/22/2021 1 1234 John Doe Drug A 10/19/2021 30 10/23/2021 -4 1234 John Doe Drug A 11/11/2021 30 11/18/2021 -7 1234 John Doe Drug B 1/2/2021 90 1234 John Doe Drug B 8/27/2021 30 4/2/2021 147 1234 John Doe Drug B 9/23/2021 30 9/26/2021 -3 1234 John Doe Drug B 10/19/2021 30 10/23/2021 -4 1234 John Doe Drug B 11/11/2021 30 11/18/2021 -7 1234 John Doe Drug B 12/22/2021 30 12/11/2021 11
2 REPLIES 2
Super User

## Re: Calculate Days Missed in between days/shifting dates

See this:

``````data have;
infile datalines dlm="09"x dsd truncover;
input
ID \$
First_Name \$
Last_Name \$
Medication \$
Fill_Date :mmddyy10.
Days_Supply
Next_Refill_Date :mmddyy10.
Days_In_Between
;
format Fill_Date Next_Refill_Date yymmdd10.;
datalines;
1234	John	Doe	Drug A	1/23/2021	90
1234	John	Doe	Drug A	4/1/2021	30	4/23/2021	-22
1234	John	Doe	Drug A	7/30/2021	30	5/1/2021	90
1234	John	Doe	Drug A	8/23/2021	30	8/29/2021	-6
1234	John	Doe	Drug A	9/23/2021	30	9/22/2021	1
1234	John	Doe	Drug A	10/19/2021	30	10/23/2021	-4
1234	John	Doe	Drug A	11/11/2021	30	11/18/2021	-7
1234	John	Doe	Drug B	1/2/2021	90
1234	John	Doe	Drug B	8/27/2021	30	4/2/2021	147
1234	John	Doe	Drug B	9/23/2021	30	9/26/2021	-3
1234	John	Doe	Drug B	10/19/2021	30	10/23/2021	-4
1234	John	Doe	Drug B	11/11/2021	30	11/18/2021	-7
1234	John	Doe	Drug B	12/22/2021	30	12/11/2021	11
;

data want;
set have;
by id medication;
retain first_fill total_supply;
format first_fill last_date yymmdd10.;
if first.medication
then do;
first_fill = fill_date;
total_supply = days_supply;
end;
else total_supply + days_supply;
if last.medication
then do;
last_date = fill_date + days_supply;
missed_days = last_date - first_fill - total_supply;
output;
end;
drop fill_date days_supply next_refill_date days_in_between;
run;
``````

It sums the total supply, calculates the total time span covered, and builds a sum and difference from that.

The result differs from what you wanted, so your logic may be different.

Notre how I presented example data in usable form that leaves no question about variable attributes and real content. Please do so in the future.

Quartz | Level 8

## Re: Calculate Days Missed in between days/shifting dates

Thank you. However, I need to be able to split the days missed by months.

So for instance for Drug A, I have -22 days missed in April. I can not have negative days.

I need to change the fill dates so that I end up with positive days missed for each month.
Discussion stats
• 2 replies
• 313 views
• 0 likes
• 2 in conversation