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!
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
Sounds like a 7 day moving average then?
Try proc expand.
If its every 7 days then try a week format with proc means.
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.
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,
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.