Solved
Contributor
Posts: 23

# Need help calculating running totals by multiple parameters

Hi,

I'm having trouble coding running totals of workhours for a cohort of people. Specifically for each observation, I want the total number of hours worked in the PRIOR 7 days. My data looks like this:

ID     Date     Hours Worked     Time_In     Time_Out

a     1/15/09     8                    8am          4pm

a     1/16/09     12                  8am          8pm

a     1/17/09     8                    12pm         8pm

a     1/17/09     6                    10pm          4am
a     1/20/09     12                    8am          8pm

b     1/15/08     8                    12pm          8pm

I have lots of people, people with separate shifts per day (I want to include the prior shifts in the total for the prior 7 days), and lots of missing dates representing weekends.

Anyone?

Thanks!

Accepted Solutions
Solution
‎02-21-2013 10:17 PM
Posts: 5,529

## Re: Need help calculating running totals by multiple parameters

If a person's split workshifts are always given in time order then this would work (otherwise you are stuck reading and interpreting AM/PM hour values) :

data test;
attrib date informat=mmddyy8. format=yymmdd10.;
input ID  \$ Date HoursWorked;
seq = _n_; /* sequence number */
datalines;
a     1/15/09     8                    8am          4pm
a     1/16/09     12                  8am          8pm
a     1/17/09     8                    12pm         8pm
a     1/17/09     6                    10pm          4am
a     1/20/09     12                    8am          8pm
b     1/15/08     8                    12pm          8pm
;

proc sql;
create table hw7days as
select a.ID, a.Date, a.HoursWorked, sum(b.hoursWorked) as hw7days
from test as a left join test as b
on a.id=b.id and
b.date between intnx("DAY",a.date,-7) and intnx("DAY",a.date,-0) and
b.seq < a.seq
group by a.ID, a.Date, a.seq, a.HoursWorked
order by a.seq;
select * from hw7days;
quit;

PG

PG

All Replies
Super User
Posts: 23,726

## Re: Need help calculating running totals by multiple parameters

Sounds like a 7 day moving average then?

Try proc expand.

If its every 7 days then try a week format with proc means.

Super User
Posts: 6,776

## Re: Need help calculating running totals by multiple parameters

So just to clarify then, it sounds like ...

The prior 7 days refers to calendar days, whether there was any work done on that day or not.

And "prior" does not include the current Date.

If that's right, it should be feasible using two arrays, but let's make sure the problem is properly defined first.

Solution
‎02-21-2013 10:17 PM
Posts: 5,529

## Re: Need help calculating running totals by multiple parameters

If a person's split workshifts are always given in time order then this would work (otherwise you are stuck reading and interpreting AM/PM hour values) :

data test;
attrib date informat=mmddyy8. format=yymmdd10.;
input ID  \$ Date HoursWorked;
seq = _n_; /* sequence number */
datalines;
a     1/15/09     8                    8am          4pm
a     1/16/09     12                  8am          8pm
a     1/17/09     8                    12pm         8pm
a     1/17/09     6                    10pm          4am
a     1/20/09     12                    8am          8pm
b     1/15/08     8                    12pm          8pm
;

proc sql;
create table hw7days as
select a.ID, a.Date, a.HoursWorked, sum(b.hoursWorked) as hw7days
from test as a left join test as b
on a.id=b.id and
b.date between intnx("DAY",a.date,-7) and intnx("DAY",a.date,-0) and
b.seq < a.seq
group by a.ID, a.Date, a.seq, a.HoursWorked
order by a.seq;
select * from hw7days;
quit;

PG

PG
Contributor
Posts: 23

## Re: Need help calculating running totals by multiple parameters

PG,

You completely solved my issue!!! This worked so beautifully.

And to Reeza and Astounding, Thank you for the responses. This is my first time posting in this forum, and I am so impressed.

BTS

🔒 This topic is solved and locked.