I presume you want the weighted average PERCENTGRIND for each bed (from dataset BEDS) where weights are determined by duration of the bed used within each period identified in your PCT_GRIND dataset (that's the name I assign it). I also presume that the PCT_GRIND date/time stamps are the start of a given level of PERCENTGRIND.
Luckily (1) your bed usages do not overlap (each END precedes the next START in the dataset (which I call BEDS), and (2) both datasets are sorted chronologically (BEDS is sorted by START, and PCT_GRIND is sorted by DATETIME). These features are assumed to this program.
Getting a weighted average is straightforward using PROC SUMMARY (also know as PROC MEANS). The problem is generating a dataset with the weights (variable DURATION in data set NEED):
data need;
set beds ;
/* Exhaust PCT_GRIND periods that cover this START-END period */
do until (datetime>end);
if (datetime<=start<=nxt_dt) or (datetime<=end<=nxt_dt) then do;
duration = 1 + min(end,nxt_dt) - max(start,datetime);
output;
end;
if nxt_dt>end then leave; ** Read no more PCT_GRIND for this bed**;
merge pct_grind
pct_grind (firstobs=2 keep=datetime rename=(datetime=nxt_dt));
format duration time10.0;
end;
run;
proc summary data=need noprint;
class bed;
var percentgrind;
weight duration;
ways 1;
output out=want mean=pctgrind_mean sumwgt=total_duration / autoname;
format total_duration time10.0;
run;
For each bed, dataset NEED gets as many records as there are overlaps between the bed usage (START to END) with the time spans in PCT_GRIND (DATETIME to NXT_DT). Each such record gets a variable called DURATION. That's where the
duration = 1 + min(end,nxt_dt) - max(start,datetime);
comes in. Note the NXT_DT is the datetime starting the next PCT_GRIND span. It is obtained by use of the "FIRSTOBS=2" parameter in the self-merge activated by the MERGE statement.
Since PERCENTGRIND is missing for some time spans, any bed use that includes that time span will include only the neighboring non-missing spans that overlaps with bed usage.
Also any bed usage that is outside of the range of times in PCT_GRIND will not be processed at all.
I recommend you examine dataset NEED to see the intent of the data step programming.
