Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-01-2017 11:31 AM
(2212 views)

Good morning

Nothing like learning a new SAS skill with a horribly complex project 🙂

I'm trying to build a 6 week Schedule Template for the nurses that work in the Recovery area for my hospital. A couple of key things about my organisation:

1) We are Monday - Friday only, without inpatients etc. (100% ambulatory/outpatient surgery and clinics)

2) Our nurses do not go to other units; they are dedicated to the Recovery area.

3) Our Nursing Union is extremely strict on the scheduling requirements, and so these need to be met as part of the schedule.

I'm trying to build a Template so that I can keep reusing the plan - holidays and closures will impact the schedule, but this template will cover 95% of the year.

Here are the constraints that I've been able to put together so far:

1) All Full Time nurses must work 4 Mondays in the 6 week schedule

2) All Full Time nurses must work 4 Fridays in the 6 week schedule

3) Full Time hours is 75 hours per 2 week pay-period, and must be met

4) All FT nurses must work the same number of 7, 10, and 12 hour shifts per 6 week cycle.

5) FT nurses can't do two weeks of 12 hour shifts, and can't have more than 3 days off in a row (excluding weekends)

6) We have 3 PT Nurses at 0.6 FTE, 1 at 0.5 FTE, and 1 at 0.4 FTE (45 hours, 37.5 (round to 38) hours and 30 hours)

7) The PT Nurses must meet their hours in a 2 pay-period, no more or less.

😎 We have 9 Full Time Nurses, 5 PT, and 7 Casual. Casuals do not need to pick up minimum hours.

9) I can only have a total of 3 shifts per day for the PT / Casual staff (ideal would be 2, but 3 shifts is OK)

10) There are 3 12-hour shifts, 3 10-hour shifts, and 7 8-hour shifts per day

I've read through A Brief Study of the Nurse Scheduling Problem (NSP), which was good but not SAS-related. I've been trying to put the constraints in terms of mathematical notation but getting lost in all the symbols; although not needed for the project, if I can have help putting the forumulae together, that would be amazing.

I've gone through the Building and Solving Optimization Models with SAS/OR Course Notes, and the SAS/OR Documentation online. Because I'm trying to constrain both rows (number of hours for each nurse) and columns (number of shifts per day), I'm going around in circles.

We don't need to take into account nurses' preferences for this template. This is just a "proof of concept" for the Nurse Manager, and I'll work with Human Resources and the leadership team to fine-tune the scheduling model.

I'd appreciate any ideas you have, or links to documentation that may help. I'm happy and willing to do the work, I just need some guidance.

Thanks for your time and have a great day

Chris

Has my article or post helped? Please mark as Solution or Like the article!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I'm not sure I have interpreted all your rules correctly. For example, does four Mondays mean exactly four or at least four? In any case, the code below should give you a nudge in the right direction.

Note that I added an objective to minimize the total number of hours worked. You can replace this with whatever you want. Also, it seems that you were missing some rule about at most one shift per day per nurse, so I added a constraint for that. Workforce scheduling problems like this usually have a constraint to make sure that some forecasted demand for each period is covered. In particular, there is nothing in this model that prevents some hour from having no staff assigned.

By the way, when I run this the resulting optimal schedule does not use any Casual nurses.

Please let me know if you have any further questions.

```
data nurse_data(drop=i);
length type $6.;
do i = 1 to 9;
nurse + 1;
type = 'FULL';
hours = 75;
output;
end;
do i = 1 to 3;
nurse + 1;
type = 'PT';
hours = 45;
output;
end;
nurse + 1;
hours = 38;
output;
nurse + 1;
hours = 30;
output;
do i = 1 to 7;
nurse + 1;
type = 'Casual';
hours = 0;
output;
end;
run;
data shift_data;
input shift length daily_count;
datalines;
1 12 3
2 10 3
3 8 7
;
proc optmodel;
num num_weeks = 6;
num num_days_per_week = 5;
set WEEKS = 0..num_weeks-1;
set DAYS = 0..num_weeks*num_days_per_week-1;
num week {d in DAYS} = floor(d/num_days_per_week);
num dow {d in DAYS} = mod(d,num_days_per_week); /* Monday = 0, ..., Friday = 4 */
str dow_name {0..4} = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri'];
print week dow {d in DAYS} dow_name[dow[d]];
set NURSES;
str nurse_type {NURSES};
num nurse_hours {NURSES};
read data nurse_data into NURSES=[nurse] nurse_type=type nurse_hours=hours;
set SHIFTS;
num shift_length {SHIFTS};
num shift_count {SHIFTS};
read data shift_data into SHIFTS=[shift] shift_length=length shift_count=daily_count;
/* Assign[n,d,s] = 1 if nurse n works on day d in shift s */
var Assign {NURSES, DAYS, SHIFTS} binary;
set FT_NURSES = {n in NURSES: nurse_type[n] = 'FULL'};
set PT_NURSES = {n in NURSES: nurse_type[n] = 'PT'};
set CASUAL_NURSES = {n in NURSES: nurse_type[n] = 'Casual'};
con Con1 {n in FT_NURSES}:
sum {d in DAYS: dow_name[dow[d]] = 'Mon'} sum {s in SHIFTS} Assign[n,d,s] >= 4;
con Con2 {n in FT_NURSES}:
sum {d in DAYS: dow_name[dow[d]] = 'Fri'} sum {s in SHIFTS} Assign[n,d,s] >= 4;
con Con3 {n in NURSES, w in 0..num_weeks-1 by 2}:
sum {d in DAYS: week[d] in {w,w+1}} sum {s in SHIFTS} shift_length[s]*Assign[n,d,s] >= nurse_hours[n];
var FTShiftCount {SHIFTS} >= 0 integer;
con Con4 {n in FT_NURSES, s in SHIFTS}:
sum {d in DAYS} Assign[n,d,s] = FTShiftCount[s];
/* at least one non 12-hour shift every 10 days */
con Con5a {n in FT_NURSES, start in DAYS: start+9 in DAYS}:
sum {d in start..start+9} sum {s in SHIFTS: shift_length[s] ne 12} Assign[n,d,s] >= 1;
/* at least one shift every four days */
con Con5b {n in FT_NURSES, start in DAYS: start+3 in DAYS}:
sum {d in start..start+3, s in SHIFTS} Assign[n,d,s] >= 1;
con Con9 {d in DAYS}:
sum {n in PT_NURSES union CASUAL_NURSES, s in SHIFTS} Assign[n,d,s] <= 3;
con Con10 {d in DAYS, s in SHIFTS}:
sum {n in NURSES} Assign[n,d,s] <= shift_count[s];
/* for each nurse, at most one shift per day */
con OneShiftPerDay {n in NURSES, d in DAYS}:
sum {s in SHIFTS} Assign[n,d,s] <= 1;
/* minimize total number of hours worked */
min TotalHours =
sum {n in NURSES, d in DAYS, s in SHIFTS} shift_length[s]*Assign[n,d,s];
/* call MILP solver */
solve;
print shift_length FTShiftCount;
print Con1.body Con1.lb;
print Con2.body Con2.lb;
print Con3.body Con3.lb;
/* create output data set */
create data solution_data from [nurse day shift]=
{n in NURSES, d in DAYS, s in SHIFTS: Assign[n,d,s].sol > 0.5}
week[d] dow=dow_name[dow[d]] nurse_type[n] shift_length[s];
quit;
proc sort data=solution_data;
by nurse day shift;
run;
```

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Here are a couple of related doc examples:

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks @RobPratt for the reading. I got an email that you posted a question about the cyclic nature, but I don't see it here. Yes, the idea is that the schedule will start on a Monday (week 1) and will finish at Week 6 and then start back again to week 1. The idea is that the nurses will then know that Week 2 of the schedule, they're doing a particular shift and can plan their lives accordingly.

I'm hoping this makes sense.

thanks again for your time

Chris

Has my article or post helped? Please mark as Solution or Like the article!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Are the shift start times predetermined, or are they supposed to be determined by the solver?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Rob

Apologies for the delay, been a rough week. The start times will be determined by the shift (12 hour is 630-1830, 8 hour is 8-4, 10 hour is 1000-1900). We are looking at adding shifts based on skill set but that'll be down the road.

Thanks and have a good day

Chris

Has my article or post helped? Please mark as Solution or Like the article!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I'm not sure I have interpreted all your rules correctly. For example, does four Mondays mean exactly four or at least four? In any case, the code below should give you a nudge in the right direction.

Note that I added an objective to minimize the total number of hours worked. You can replace this with whatever you want. Also, it seems that you were missing some rule about at most one shift per day per nurse, so I added a constraint for that. Workforce scheduling problems like this usually have a constraint to make sure that some forecasted demand for each period is covered. In particular, there is nothing in this model that prevents some hour from having no staff assigned.

By the way, when I run this the resulting optimal schedule does not use any Casual nurses.

Please let me know if you have any further questions.

```
data nurse_data(drop=i);
length type $6.;
do i = 1 to 9;
nurse + 1;
type = 'FULL';
hours = 75;
output;
end;
do i = 1 to 3;
nurse + 1;
type = 'PT';
hours = 45;
output;
end;
nurse + 1;
hours = 38;
output;
nurse + 1;
hours = 30;
output;
do i = 1 to 7;
nurse + 1;
type = 'Casual';
hours = 0;
output;
end;
run;
data shift_data;
input shift length daily_count;
datalines;
1 12 3
2 10 3
3 8 7
;
proc optmodel;
num num_weeks = 6;
num num_days_per_week = 5;
set WEEKS = 0..num_weeks-1;
set DAYS = 0..num_weeks*num_days_per_week-1;
num week {d in DAYS} = floor(d/num_days_per_week);
num dow {d in DAYS} = mod(d,num_days_per_week); /* Monday = 0, ..., Friday = 4 */
str dow_name {0..4} = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri'];
print week dow {d in DAYS} dow_name[dow[d]];
set NURSES;
str nurse_type {NURSES};
num nurse_hours {NURSES};
read data nurse_data into NURSES=[nurse] nurse_type=type nurse_hours=hours;
set SHIFTS;
num shift_length {SHIFTS};
num shift_count {SHIFTS};
read data shift_data into SHIFTS=[shift] shift_length=length shift_count=daily_count;
/* Assign[n,d,s] = 1 if nurse n works on day d in shift s */
var Assign {NURSES, DAYS, SHIFTS} binary;
set FT_NURSES = {n in NURSES: nurse_type[n] = 'FULL'};
set PT_NURSES = {n in NURSES: nurse_type[n] = 'PT'};
set CASUAL_NURSES = {n in NURSES: nurse_type[n] = 'Casual'};
con Con1 {n in FT_NURSES}:
sum {d in DAYS: dow_name[dow[d]] = 'Mon'} sum {s in SHIFTS} Assign[n,d,s] >= 4;
con Con2 {n in FT_NURSES}:
sum {d in DAYS: dow_name[dow[d]] = 'Fri'} sum {s in SHIFTS} Assign[n,d,s] >= 4;
con Con3 {n in NURSES, w in 0..num_weeks-1 by 2}:
sum {d in DAYS: week[d] in {w,w+1}} sum {s in SHIFTS} shift_length[s]*Assign[n,d,s] >= nurse_hours[n];
var FTShiftCount {SHIFTS} >= 0 integer;
con Con4 {n in FT_NURSES, s in SHIFTS}:
sum {d in DAYS} Assign[n,d,s] = FTShiftCount[s];
/* at least one non 12-hour shift every 10 days */
con Con5a {n in FT_NURSES, start in DAYS: start+9 in DAYS}:
sum {d in start..start+9} sum {s in SHIFTS: shift_length[s] ne 12} Assign[n,d,s] >= 1;
/* at least one shift every four days */
con Con5b {n in FT_NURSES, start in DAYS: start+3 in DAYS}:
sum {d in start..start+3, s in SHIFTS} Assign[n,d,s] >= 1;
con Con9 {d in DAYS}:
sum {n in PT_NURSES union CASUAL_NURSES, s in SHIFTS} Assign[n,d,s] <= 3;
con Con10 {d in DAYS, s in SHIFTS}:
sum {n in NURSES} Assign[n,d,s] <= shift_count[s];
/* for each nurse, at most one shift per day */
con OneShiftPerDay {n in NURSES, d in DAYS}:
sum {s in SHIFTS} Assign[n,d,s] <= 1;
/* minimize total number of hours worked */
min TotalHours =
sum {n in NURSES, d in DAYS, s in SHIFTS} shift_length[s]*Assign[n,d,s];
/* call MILP solver */
solve;
print shift_length FTShiftCount;
print Con1.body Con1.lb;
print Con2.body Con2.lb;
print Con3.body Con3.lb;
/* create output data set */
create data solution_data from [nurse day shift]=
{n in NURSES, d in DAYS, s in SHIFTS: Assign[n,d,s].sol > 0.5}
week[d] dow=dow_name[dow[d]] nurse_type[n] shift_length[s];
quit;
proc sort data=solution_data;
by nurse day shift;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I'm currently at home with food poisoning (I think that's what it is at least) so won't have a chance to play with this till Monday, but *holy cow* this is awesome.

Thanks for the feedback re: Constraints etc. I am really excited to use this to help my organization move forward, and excited to see what I can learn.

All the best and have a great weekend

Chris

Has my article or post helped? Please mark as Solution or Like the article!

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.