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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.