Mail <caging@FINEARTS.UVIC.CA>, in particular, wrote:

>The following is my complete request: >I have a dataset which has the following fields: > >date time period hour wkday >4/5/98 1 3 1 >4/5/98 2 1 1 >4/6/98 1 2 2 >4/7/98 1 2 3 >4/7/98 2 1 3 >etc. > >wkday is sas weekday. I want to calculate the mean hours of the previous >seven days starting from 4/12/98 and go on in the same manner for each >following day after 4/12 and output the mean hours into to another >dataset. In other words, I want to calculate the mean hours for the seven >day period (not mean hours for each day of the seven days) prior to 4/12 >(that is, 4/5 to 4/11) by time periods, then go on to do the next seven >day period from 4/6 to 4/12, then 4/7 to 4/13, and so on.

Mail,

One tactic you may use is as follows:

1) Collapse date by-groups computing day-hours for the same day. 2) For each of the seven days prior to start date, i.e. 4/12/98 in your case, simply sum the day-hours as you go through observations by adding day-hours corresponding to the previous date to a total. 3) At the same time, create an 8-element queue, so that when you arrive at 4/13/98, the very first value that has been added to form the total at 4/12/98 will be ejected from the queue. 4) Beginning from 4/13/98, add the day-hours for the preceding day, and subtract the ejected value.

Some sample data first (skipping period and wkday as being dropped anyway):

DATA HOURS (DROP=I); DO DATE='03APR98'D TO '21MAY98'D; DO I=1 TO CEIL(RANUNI(1)*4); HOURS = CEIL(RANUNI(1)*24); OUTPUT; END; END; RUN; PROC SORT; BY DATE; RUN;

Let us say you want to report on dates from 4/12/98 to 4/30/98:

%LET DT_BEG = %SYSEVALF('12APR98'D); %LET DT_END = %SYSEVALF('30APR98'D);

Below, I assume that you report on the period of, say 4/09/98 through 4/16/98, as of 4/17/98. Now we can execute the plan outlined above:

DATA MEAN (WHERE=(DATE BETWEEN &D_BEG AND &D_END) KEEP=DATE MEAN); SET HOURS (WHERE=(DATE > &D_BEG-8)); BY DATE; IF FIRST.DATE THEN DAY_HRS = 0; DAY_HRS + HOURS; IF NOT LAST.DATE THEN DELETE; MEAN ++ (MAX(LAG(DAY_HRS),0)-MAX(LAG8(DAY_HRS),0)*(DATE > &D_BEG))/7; RUN;

MAX functions replace missing values ejected from either queue before it has come a full cycle with zeroes. If you would like to report a date period of A through B as of B instead of B+1 (as above), simply add the instruction

DATE +- 1;

at the bottom of the DATA step.

Kind regards, ================= Paul M. Dorfman Jacksonville, FL =================