Calculating days late to refill

[ssitharath0420]

Hello,

I am trying to calculate the Days Late to Refill by ID1 and the corresponding to each Product.

This is the dataset:

data temp;
input ID1 ID2 Product fill_dt : mmddyy10. days_supply end_dt : mmddyy10.;

format fill_dt end_dt mmddyy10.;
datalines;
117 2025 A 12/13/2019 90 3/12/2020
117 2025 A 3/10/2020 90 6/8/2020
117 2025 A 6/9/2020 90 9/7/2020
117 2025 A 9/8/2020 22 9/30/2020
117 2025 B 10/22/2019 90 1/20/2020
117 2025 B 1/21/2020 90 4/20/2020
117 2025 B 4/14/2020 90 7/13/2020
117 2025 B 8/6/2020 55 9/30/2020
117 2025 C 11/26/2019 90 2/24/2020
117 2025 C 2/25/2020 90 5/25/2020
117 2025 C 5/26/2020 90 8/24/2020
117 2025 C 8/31/2020 30 9/30/2020

;
run;

I have a code that I have written:

data temp_2;
set temp;
by ID1 ID2 Product Fill_dt End_Dt;
if first.ID1 then _iorc_=end_dt;
else do;
days=fill_dt-_iorc_;
_iorc_=end_dt;
end;
run;

And the output looks like this:

ID1 ID2 Product fill_dt days_supply end_dt days
117 2025 A 12/13/2019 90 3/12/2020
117 2025 A 3/10/2020 90 6/8/2020 -2
117 2025 A 6/9/2020 90 9/7/2020 1
117 2025 A 9/8/2020 22 9/30/2020 1
117 2025 B 10/22/2019 90 1/20/2020 -344
117 2025 B 1/21/2020 90 4/20/2020 1
117 2025 B 4/14/2020 90 7/13/2020 -6
117 2025 B 8/6/2020 55 9/30/2020 24
117 2025 C 11/26/2019 90 2/24/2020 -309
117 2025 C 2/25/2020 90 5/25/2020 1
117 2025 C 5/26/2020 90 8/24/2020 1
117 2025 C 8/31/2020 30 9/30/2020 7

But I want it to look like this:

ID1 ID2 Product fill_dt days_supply end_dt days
117 2025 A 12/13/2019 90 3/12/2020 .
117 2025 A 3/10/2020 90 6/8/2020 -2
117 2025 A 6/9/2020 90 9/7/2020 1
117 2025 A 9/8/2020 22 9/30/2020 1
117 2025 B 10/22/2019 90 1/20/2020 .
117 2025 B 1/21/2020 90 4/20/2020 1
117 2025 B 4/14/2020 90 7/13/2020 -6
117 2025 B 8/6/2020 55 9/30/2020 24
117 2025 C 11/26/2019 90 2/24/2020 .
117 2025 C 2/25/2020 90 5/25/2020 1
117 2025 C 5/26/2020 90 8/24/2020 1
117 2025 C 8/31/2020 30 9/30/2020 7

Thank you in advance for helping.

[mkeintz]

Let's do first things first - namely provide working code in the DATA HAVE step (and put it in a SAS code box using the "running man" symbol at the top of every message window.

I ran your DATA HAVE step.  Here are the results:

2   data temp;
23     input ID1 ID2 Product fill_dt : mmddyy10. days_supply end_dt : mmddyy10.;
24     format fill_dt end_dt mmddyy10.;
25   datalines;

NOTE: Invalid data for Product in line 26 10-10.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
26         117 2025 A 12/13/2019 90 3/12/2020
ID1=117 ID2=2025 Product=. fill_dt=12/13/2019 days_supply=90 end_dt=03/12/2020 _ERROR_=1 _N_=1
NOTE: Invalid data for Product in line 27 10-10.
27         117 2025 A 3/10/2020 90 6/8/2020
ID1=117 ID2=2025 Product=. fill_dt=03/10/2020 days_supply=90 end_dt=06/08/2020 _ERROR_=1 _N_=2
NOTE: Invalid data for Product in line 28 10-10.
28         117 2025 A 6/9/2020 90 9/7/2020

... and so on.

Also, perhaps you could point out the differences between what you want and what you got.  It is not so evident to me, and I imagine some others.

Help us help you.

[maguiremq]

As mentioned above, you have a couple formatting issues. Nonetheless, this should provide you your answer, but it depends on the sorting.

data temp;
input ID1 ID2 Product $1. fill_dt :mmddyy10. days_supply end_dt :mmddyy10.;
format fill_dt end_dt mmddyy10.;
datalines;
117 2025 A 12/13/2019 90 3/12/2020
117 2025 A 3/10/2020 90 6/8/2020
117 2025 A 6/9/2020 90 9/7/2020
117 2025 A 9/8/2020 22 9/30/2020
117 2025 B 10/22/2019 90 1/20/2020
117 2025 B 1/21/2020 90 4/20/2020
117 2025 B 4/14/2020 90 7/13/2020
117 2025 B 8/6/2020 55 9/30/2020
117 2025 C 11/26/2019 90 2/24/2020
117 2025 C 2/25/2020 90 5/25/2020
117 2025 C 5/26/2020 90 8/24/2020
117 2025 C 8/31/2020 30 9/30/2020
;
run;

proc sort data = temp;
	by id1 id2 product fill_dt;
run;

data want;
	set temp;
	by id1 id2 product fill_dt;
		lag_end_dt = lag(end_dt);
		if first.product then call missing(lag_end_dt);
		if not missing(lag_end_dt) then do;
			days = intck("days", lag_end_dt, fill_dt);
		end;
	format lag_end_dt mmddyy10.;
run;

[ssitharath0420]

Thank you! It worked!

[maguiremq]

Great, I'm glad it helped. Please mark the response as the correct solution so it can help others in the future.