SAS Optimization, and SAS Simulation Studio

turn on suggestions

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

Showing results for

Find a Community

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2017 11:31 AM

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.

8) 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!

Accepted Solutions

Solution

11-10-2017
07:52 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DarthPathos

11-08-2017 05:07 PM

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;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DarthPathos

11-01-2017 11:46 AM

Here are a couple of related doc examples:

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RobPratt

11-02-2017 08:04 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DarthPathos

11-02-2017 08:27 PM

Yes, I had posted asking whether the schedule should be cyclic in the sense that a shift that starts near the end of the six weeks should wrap around to the beginning of the six weeks. But then I reread that the nurses work Monday-Friday and realized that this can't happen because no shift is that long to start on Friday and end on Monday. So I deleted the post. Sorry for the confusion.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DarthPathos

11-03-2017 05:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RobPratt

11-08-2017 02:30 PM

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!

Solution

11-10-2017
07:52 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DarthPathos

11-08-2017 05:07 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RobPratt

11-10-2017 07:51 AM

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!