topic Re: Need help calculating running totals by multiple parameters in SAS Procedures

Need help calculating running totals by multiple parameters

bts — Thu, 21 Feb 2013 20:55:09 GMT

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!

Re: Need help calculating running totals by multiple parameters

Reeza — Thu, 21 Feb 2013 22:32:39 GMT

Sounds like a 7 day moving average then?

Try proc expand.

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

Re: Need help calculating running totals by multiple parameters

Astounding — Fri, 22 Feb 2013 02:04:56 GMT

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.

Re: Need help calculating running totals by multiple parameters

PGStats — Fri, 22 Feb 2013 03:17:40 GMT

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;

Re: Need help calculating running totals by multiple parameters

bts — Fri, 22 Feb 2013 13:58:48 GMT

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