Average of observations between multiple dates based on an indicator

[Alireza_Boloori]

Hello everyone,

I have a data set of patients who use a medications with different dose at different times. The patients may also experience an event (i.e., hospital readmission) at multiple times. Here is a part of the data (for patient #1):

data have;
input id date drug_dose indicator;
datalines;
1 2008-01-17 7.5  0
1 2008-01-18 14.7 0
1 2008-01-19 21.5 0
1 2008-01-20 27.3 0
1 2008-01-21 32.1 0
1 2008-01-22 35.9 0
1 2008-01-23 38.6 0
1 2008-01-24 40.6 0
1 2008-01-25 41.8 0
1 2008-01-26 42.6 0
1 2008-02-06 0.0  1 
1 2008-03-01 129.4 0
1 2008-03-02 107.7 0
1 2008-03-03 86.2  0
1 2008-03-04 34.4  0
1 2008-03-05 9.1   0
1 2008-03-19 0.0   1
;

where indicator 0 and 1 represents "no event" and "event", respectively. Now, I want to measure the ratio of (total cumulative dose used between each two events) and (the total time spanned between these two events). Something like this:

data want;
input id avg_dose time_start time_stop;
datalines;
1 15.13 2008-1-17 2008-2-6 
1 13.585 2008-2-7 2008-3-5
1 0.0 2008-3-6 2008-3-19
;

For example, 15.13 = 302.6/20,  and 302.6 is the total cumulative dose used from the beginning (2008-1-17) and the time of the first event (2008-2-6), and 20 is the total number of days spanned over between the foregoing two dates.

Any idea/thought is really appreciated!

[ballardw]

What is the rule that indicates the second series end on 2008-3-5?

data want;
input id avg_dose time_start time_stop;
datalines;
1 15.13 2008-1-17 2008-2-6 
1 13.585 2008-2-7 2008-3-5
1 0.0 2008-3-6 2008-3-19
;

I would start with something like:

data want;
   set have;
   by id date;
   retain date_start cum_dose;
   if first.id then date_start=date;
   if indicator=0 then cum_dose+drug_dose;
   if indicator=1 then do;
      date_stop = date;
      avg_dose= cum_dose/(date_stop - date_start);
      output;
      date_start=date+1;
   end;
   format date_stop  date_start yymmdd10.;
   drop date indicator cum_dose drug_dose;
run;

but that doesn't work for your stop at 2008-3-5.

[Alireza_Boloori]

@ballardw Thank you very much! Yes, you're right. That was my mistake in writing the original code. There are a couple of points though, and I'd be thankful if you can help me as well:

1. Your code only considers the last event (that's because I only provided data for patient 1). However, there are patients that use medications after an event. In that case, the "date_stop" will be the last day of using that medication.
2. I didn't write all of my variables in the original code (for the sake of simplicity). When I run your code for a data set (with all of my variables in it), all those variables will get "." instead of their characters/numerics. 

I was wondering how I can address these two points as well. Once again, thanks for your time and help!

[Reeza]

Adding a link back to your original question that has more details. 

https://communities.sas.com/t5/SAS-Studio/Average-of-observations-between-multiple-specific-dates/m-...

[Ksharp · Accepted solution]

data have;
input id date : yymmdd10. drug_dose indicator;
format  date yymmdd10.;
datalines;
1 2008-01-17 7.5  0
1 2008-01-18 14.7 0
1 2008-01-19 21.5 0
1 2008-01-20 27.3 0
1 2008-01-21 32.1 0
1 2008-01-22 35.9 0
1 2008-01-23 38.6 0
1 2008-01-24 40.6 0
1 2008-01-25 41.8 0
1 2008-01-26 42.6 0
1 2008-02-06 0.0  1 
1 2008-03-01 129.4 0
1 2008-03-02 107.7 0
1 2008-03-03 86.2  0
1 2008-03-04 34.4  0
1 2008-03-05 9.1   0
1 2008-03-19 0.0   1
;

data have;
 set have;
 by id;
 if first.id or (indicator=0 and lag(indicator)=1) then group+1;
 run;
 proc sql;
 select id,min(date) as start format=yymmdd10.,max(date) as stop format=yymmdd10.,
  sum(drug_dose) as sum,
  calculated sum/(calculated stop-calculated start) as ratio
  from have
   group by id,group;
quit;