BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bts
Calcite | Level 5 bts
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

4 REPLIES 4
Reeza
Super User

Sounds like a 7 day moving average then?

Try proc expand.

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

Astounding
PROC Star

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.

PGStats
Opal | Level 21

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
bts
Calcite | Level 5 bts
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 736 views
  • 3 likes
  • 4 in conversation