Rollover values across cases

[djohn051]

DATA TEST; 
INPUT ID $ SUPPLY DATE_RECEIVED :DATE9.; 
FORMAT DATE_RECEIVED DDMMYY10.; 
DATALINES; 
A 14 20AUG2018 
A 14 31AUG2018 
A 90 10SEP2018 
A 60 17DEC2018 
B 28 21FEB2018 
B 22 21MAR2018 
B 30 05JUN2018 
B 30 24JUL2018 
B 30 20AUG2018 
B 30 05OCT2018 ; 
RUN; 

The following data is the days supply of medication (SUPPLY) which was provided on a specific date (DATE_RECEIVED) for each unique patient (ID). What I need to do is flag intervals where an individual went >= 105 days without medication.

Because an individual can receive medication while still having remaining supply, I need the days supply to rollover to each subsequent case. The exception to this is that there cannot be negative amounts of medication carried over. If it is negative then it is simply 0. This is because an individual isn’t going to take extra medication to make up for what they missed.

For example, ID ‘A’ would have enough medication to last until 03SEP2018 at their first dispense. Therefore at their second dispense they should have 17 days supply instead of 14 (SUPPLY+(03SEP2018-31AUG2018)), etc.

So what I need is to keep the surplus rolling over and flag any instance where the individual went 105 days or more without having any supply. Ideally I want to flag every case after this is identified (up to LAST.ID). This is because I will create a flatfile where each time the >=105-day gape is identified, that is the start of a new observation. So I want to pull out every subsequent case and rerun the code to find the next >=105 day gap.

[djohn051]

Please let me know if you need any additional information. I imagine this isn't the easiest problem to solve.

[PaigeMiller · Accepted solution]

Actually, in my opinion, this is one of the most clearly written problem descriptions I have seen. And thank you for providing the data as a SAS data step.

The key to doing this is to use the LAG function:

data test1;
	set test;
	prev_id=lag(id);
	prev_date=lag(date_received);
	prev_supply=lag(supply);
	if prev_id=id then do;
		delta_days=date_received-prev_date;
		cumulative_supply=supply+(prev_supply-delta_days);
                if cumulative_supply<-105 then flag=1;
                if cumulative_supply<0 then cumulative_supply=0;
	end;
	drop prev:;
run;

[ballardw]

Same basic approach, different choice of operators and operations:

data want;
   set test;
   by id;
   carryover= max(0,lag(supply)-dif(DATE_RECEIVED));
   cum_supply = sum(supply,carryover);
   flag = (dif(DATE_RECEIVED - lag(cum_supply)) ge 105)  ;
   if first.id then call missing(carryover,flag);

run;