Resources

Resource Scheduling w/ Nonstandard Temporal Constraints

/***************************************************************************/
/*                                                                         */
/*          S A S   S A M P L E   L I B R A R Y                            */
/*                                                                         */
/*    NAME: clpe09                                                         */
/*   TITLE: Resource Scheduling w/ Nonstandard Temporal Constraints        */
/*                                                                (clpe09) */
/* PRODUCT: OR                                                             */
/*  SYSTEM: ALL                                                            */
/*    KEYS: OR                                                             */
/*   PROCS: CLP, GANTT, NETDRAW, SQL                                       */
/*    DATA:                                                                */
/*                                                                         */
/* SUPPORT:                             UPDATE:                            */
/*     REF:                                                                */
/*    MISC: Example 9 from the CLP Procedure chapter of the                */
/*          Constraint Programming book.                                   */
/*                                                                         */
/***************************************************************************/

%macro drawPiling(center=49, operator=-, offset=4);
   %let barWidth=0.8;
   %let yStart=31;
   %bar(%sysevalf(&center &operator &offset), &yStart,
        %sysevalf(&center &operator &offset &operator &barWidth),
        %sysevalf(&yStart - &barHeight), black, 0, SOLID);
%mend drawPiling;
%macro drawPilings(operator=-);
   %let barHeight=10;
   %drawPiling(operator=&operator);
   %drawPiling(operator=&operator, offset=9.2);
   %let barHeight=12;
   %drawPiling(operator=&operator, offset=5.5);
   %drawPiling(operator=&operator, offset=7.7);
   %let x = 43.4;
   %let y = 17;
   %label(&x, &y, 'p1', black, 0, 0, 2.5, , 4);
   %let x = 57.1;
   %label(&x, &y, 'p2', black, 0, 0, 2.5, , 4);
%mend;
%macro drawClay(center=49, operator=-);
   %let color = CXFFD090;
   %let top = 76;
   %poly(%sysevalf(&center &operator &center &operator -1),
         &top, &color, MSOLID, 1);
   %polycont(%sysevalf(&center &operator &center &operator -1), 5, &color);
   %polycont(&center, 5, &color);
   %polycont(&center, %sysevalf(&top/2.0 +2), &color);
   %polycont(%sysevalf(&center &operator (&center/2.0) &operator 2),
             %sysevalf(&top/2.0 + 2), &color);
   %polycont(%sysevalf(&center &operator (&center/2.0) &operator 3),
             %sysevalf(&top/2.0 + 12), &color);
   %polycont(%sysevalf(&center &operator (&center/2.0) &operator 10),
             %sysevalf(&top/2.0 + 12), &color);
%mend;
%macro drawBar(type=, drawBorder=1, drawFill=1, color=);
   %bar(%sysevalf(&x+&xStart), &y,
        %sysevalf(&x + &barWidth + &xStart),
        %sysevalf(&y + &barHeight),
        &color, 0, &type);
%mend drawBar;
%macro drawBars(color=, drawBorder=1, drawFill=1, pattern=MEMPTY,
                hashColor=black);
   %if &drawFill %then %do;
       %drawBar(type=SOLID, drawFill=1, color=&color);
   %end;
   %if &drawBorder %then %do;
       %drawQuad(
           %sysevalf(&x+&xStart), &y,
           %sysevalf(&x + &barWidth + &xStart), &y,
           %sysevalf(&x + &barWidth + &xStart), %sysevalf(&y + &barHeight),
           %sysevalf(&x+&xStart), %sysevalf(&y + &barHeight),
           color=&color, pattern=&pattern, hashColor=&hashColor
       );
   %end;
%mend drawBars;
%macro drawLabel(char=, num=0);
   %label(%sysevalf((&x + &barWidth)/2.0 * &xLabelFactor +
             &xLabelOffset + &xStart),
          %sysevalf((&y + &barHeight)/2.0 * &yLabelFactor),
          "&char.&num", black, 0, 0, 2.5, , 4);
%mend drawLabel;
%macro drawBoxes(char=, count=, color=, drawBorder=1, drawFill=1,
                 pattern=MEMPTY, hashColor=black);
   %do i = 1 %to &count;
       %let x = &i * (&barWidth + &barSeparation);
       %let y = &yStart;
       %drawBars(color=&color, drawBorder=&drawBorder, drawFill=&drawFill,
                 pattern=&pattern, hashColor=&hashColor);
       %if &char ne %then %do;
          %drawLabel(char=&char, num=%eval(&i.+5-&count));
       %end;
   %end;
%mend;
%macro drawTri(x1, y1, x2, y2, x3, y3, color=);
   %poly(&x1, &y1, &color, MSOLID, 1);
   %polycont(&x2, &y2, &color);
   %polycont(&x3, &y3, &color);
   %poly(&x1, &y1, black, M5X45, 1);
   %polycont(&x2, &y2, black);
   %polycont(&x3, &y3, black);
%mend;
%macro drawQuad(x1, y1, x2, y2, x3, y3, x4, y4, color=, pattern=MEMPTY,
                hashColor=black);
   %poly(&x1, &y1, &color, MSOLID, 1);
   %polycont(&x2, &y2, &color);
   %polycont(&x3, &y3, &color);
   %polycont(&x4, &y4, &color);
   %poly(&x1, &y1, &hashColor, &pattern, 1);
   %polycont(&x2, &y2, black);
   %polycont(&x3, &y3, black);
   %polycont(&x4, &y4, black);
%mend;
%macro drawQuads(count=, color=, pattern=);
   %let y = 40;
   %do i = &count %to 1 %by -1;
      %let x = %eval(&i * 14);
      %drawQuad(%sysevalf(&x + 8.5), &y,
                %sysevalf(&x + 19.5), &y,
                %sysevalf(&x + 18.5), %sysevalf(&y - 9),
                %sysevalf(&x + 9.5), %sysevalf(&y - 9),
                color=&color, pattern=&pattern);
   %end;
%mend;
%macro drawSide(center=49, textCenter=42.3, operator=-, num1=1, num2=1);
   /* draw V1 */
   %let y=75.9;
   %drawTri(%sysevalf(&center &operator 30), &y,
            %sysevalf(&center &operator 48), &y,
            %sysevalf(&center &operator 38), %sysevalf(&y-25.9),
            color=&sand
   );
   %let y = %sysevalf(&y - 17.9);
   %let x = %sysevalf(&textCenter &operator 45);
   %drawLabel(char=v, num=&num1);
   /* draw B1 */
   %let y = 58;
   %drawQuad(%sysevalf(&center &operator 29), &y,
             %sysevalf(&center &operator 38), &y,
             %sysevalf(&center &operator 38), %sysevalf(&y - 8),
             %sysevalf(&center &operator 29), %sysevalf(&y - 8),
             color=CXA0A0A0
   );
   %let x = %sysevalf(&textCenter &operator 33.5);
   %let y = %sysevalf(&y - 6);
   %drawLabel(char=b, num=&num2);
   %let y = %sysevalf(&y - 3);
   %drawLabel(char=s, num=&num2);
   /* draw M1 */
   %let y = 70;
   %drawQuad(%sysevalf(&center &operator 30), &y,
             %sysevalf(&center &operator 35), &y,
             %sysevalf(&center &operator 37), %sysevalf(&y - 12),
             %sysevalf(&center &operator 30), %sysevalf(&y - 12),
             color=ltgray, pattern=M1L0, hashColor=CXD0D0D0
   );
   %let x = %sysevalf(&textCenter &operator 33);
   %let y = %sysevalf(&y - 10);
   %drawLabel(char=m, num=&num2);
   /* label A1-A6 */
   %let x = %sysevalf(&textCenter &operator 33.5);
   %let y = %sysevalf(&y - 15.7);
   %drawLabel(char=a, num=&num2);
   %let x = %sysevalf(&textCenter &operator 21.0);
   %let y = %sysevalf(&y - 19.5);
   %drawLabel(char=a, num=%eval(&num2 &operator -1));
   %let x = %sysevalf(&textCenter &operator 13.0);
   %drawLabel(char=a, num=%eval(&num2 &operator -2));
%mend drawSide;
%macro drawSky;
   %bar(1, 10, 97, 100, white, 0, SOLID);
   %bar(0.85, 4.7, 97.15, 100, black, 0, EMPTY);
%mend drawSky;
%macro drawBridge;
%annomac;
data anno_bridge;
   %dclanno;
   %system(3,3,3);
   %drawSky;
   %drawClay;
   %drawClay(operator=+);
   %drawPilings;
   %drawPilings(operator=+);
   %let barWidth = 14;
   %let barHeight = 6;
   %let yLabelFactor = 1.93;
   %let xLabelFactor = 2.0;
   %let xLabelOffset = -6;
   %let yStart = 70;
   %let xStart = 0;
   %let barSeparation = 0;
   %let sand = CXF8EABF;
   %drawSide;
   %drawSide(operator=+, num1=2, num2=6);
   /* draw sand buckets */
   %drawQuads(color=&sand, count=4, pattern=M5X45);
   /* draw T1-T5 */
   %drawBoxes(char=t, count=5, color=CX80A0DF);
   %let barWidth = 4;
   %let barHeight = 30;
   %let yLabelFactor = 1.6;
   %let xLabelFactor = 2.0;
   %let xLabelOffset = -0.6;
   %let yStart = 40;
   %let barSeparation = 10;
   %let xStart = 12;
   /* draw M2-M5 */
   %drawBoxes(char=m, count=4, color=ltgray, pattern=M1L0,
              hashColor=CXD0D0D0);
   %let barWidth = 6;
   %let yLabelFactor = 1.8;
   %let xLabelFactor = 2.0;
   %let xLabelOffset = -1.8;
   %let yStart = 31;
   %let barSeparation = 8;
   %let xStart = 11;
   %let barHeight = 7.5;
   /* draw B2-B5 */
   %drawBoxes(char=s, count=4, color=CXA0A0A0, drawBorder=0);
   %let barHeight = 11;
   %drawBoxes(char=b, count=4, color=CX000000, drawBorder=0, drawFill=0);
   %let barHeight = 9;
   %drawBoxes(char=, count=4, color=CXA0A0A0);
run;
proc ganno annotate=anno_bridge;
run;
quit;
%mend drawBridge;
option nodate nonumber;
%drawBridge;

data bridge;
   format _ACTIVITY_ $3. _DESC_ $34. _RESOURCE_ $14.
          _SUCCESSOR_ $3. _LAG_ $3. ;
   input _ACTIVITY_ & _DESC_ & _DURATION_ _RESOURCE_ &
         _SUCCESSOR_ & _LAG_ & _LAGDUR_;
   _QTY_ = 1;
   datalines;
a1   excavation (abutment 1)             4  excavator       s1   .     .
a2   excavation (pillar 1)               2  excavator       s2   .     .
a3   excavation (pillar 2)               2  excavator       p1   .     .
a4   excavation (pillar 3)               2  excavator       p2   .     .
a5   excavation (pillar 4)               2  excavator       s5   .     .
a6   excavation (abutment 2)             5  excavator       s6   .     .
ab1  concrete setting time (abutment 1)  1  .               m1   .     .
ab2  concrete setting time (pillar 1)    1  .               m2   .     .
ab3  concrete setting time (pillar 2)    1  .               m3   .     .
ab4  concrete setting time (pillar 3)    1  .               m4   .     .
ab5  concrete setting time (pillar 4)    1  .               m5   .     .
ab6  concrete setting time (abutment 2)  1  .               m6   .     .
b1   concrete foundation (abutment 1)    1  concrete mixer  ab1  .     .
b1   concrete foundation (abutment 1)    1  concrete mixer  s1   ff   -4
b2   concrete foundation (pillar 1)      1  concrete mixer  ab2  .     .
b2   concrete foundation (pillar 1)      1  concrete mixer  s2   ff   -4
b3   concrete foundation (pillar 2)      1  concrete mixer  ab3  .     .
b3   concrete foundation (pillar 2)      1  concrete mixer  s3   ff   -4
b4   concrete foundation (pillar 3)      1  concrete mixer  ab4  .     .
b4   concrete foundation (pillar 3)      1  concrete mixer  s4   ff   -4
b5   concrete foundation (pillar 4)      1  concrete mixer  ab5  .     .
b5   concrete foundation (pillar 4)      1  concrete mixer  s5   ff   -4
b6   concrete foundation (abutment 2)    1  concrete mixer  ab6  .     .
b6   concrete foundation (abutment 2)    1  concrete mixer  s6   ff   -4
l    delivery of the preformed bearers   2  crane           t1   .     .
l    delivery of the preformed bearers   2  crane           t2   .     .
l    delivery of the preformed bearers   2  crane           t3   .     .
l    delivery of the preformed bearers   2  crane           t4   .     .
l    delivery of the preformed bearers   2  crane           t5   .     .
m1   masonry work (abutment 1)          16  bricklaying     t1   .     .
m1   masonry work (abutment 1)          16  bricklaying     ua   fs   -2
m2   masonry work (pillar 1)             8  bricklaying     t1   .     .
m2   masonry work (pillar 1)             8  bricklaying     t2   .     .
m2   masonry work (pillar 1)             8  bricklaying     ua   fs   -2
m3   masonry work (pillar 2)             8  bricklaying     t2   .     .
m3   masonry work (pillar 2)             8  bricklaying     t3   .     .
m3   masonry work (pillar 2)             8  bricklaying     ua   fs   -2
m4   masonry work (pillar 3)             8  bricklaying     t3   .     .
m4   masonry work (pillar 3)             8  bricklaying     t4   .     .
m4   masonry work (pillar 3)             8  bricklaying     ua   fs   -2
m5   masonry work (pillar 4)             8  bricklaying     t4   .     .
m5   masonry work (pillar 4)             8  bricklaying     t5   .     .
m5   masonry work (pillar 4)             8  bricklaying     ua   fs   -2
m6   masonry work (abutment 2)          20  bricklaying     t5   .     .
m6   masonry work (abutment 2)          20  bricklaying     ua   fs   -2
p1   foundation piles 2                 20  pile driver     s3   .     .
p2   foundation piles 3                 13  pile driver     s4   .     .
pa   beginning of project                0  .               a1   .     .
pa   beginning of project                0  .               a2   .     .
pa   beginning of project                0  .               a3   .     .
pa   beginning of project                0  .               a4   .     .
pa   beginning of project                0  .               a5   .     .
pa   beginning of project                0  .               a6   .     .
pa   beginning of project                0  .               l    fse  30
pa   beginning of project                0  .               ue   .     .
pe   end of project                      0  .               .    .     .
s1   formwork (abutment 1)               8  carpentry       b1   .     .
s1   formwork (abutment 1)               8  carpentry       a1   sf   -3
s2   formwork (pillar 1)                 4  carpentry       b2   .     .
s2   formwork (pillar 1)                 4  carpentry       a2   sf   -3
s3   formwork (pillar 2)                 4  carpentry       b3   .     .
s3   formwork (pillar 2)                 4  carpentry       p1   sf   -3
s4   formwork (pillar 3)                 4  carpentry       b4   .     .
s4   formwork (pillar 3)                 4  carpentry       p2   sf   -3
s5   formwork (pillar 4)                 4  carpentry       b5   .     .
s5   formwork (pillar 4)                 4  carpentry       a5   sf   -3
s6   formwork (abutment 2)              10  carpentry       b6   .     .
s6   formwork (abutment 2)              10  carpentry       a6   sf   -3
t1   positioning (preformed bearer 1)   12  crane           v1   .     .
t2   positioning (preformed bearer 2)   12  crane           pe   .     .
t3   positioning (preformed bearer 3)   12  crane           pe   .     .
t4   positioning (preformed bearer 4)   12  crane           pe   .     .
t5   positioning (preformed bearer 5)   12  crane           v2   .     .
ua   removal of the temporary housing   10  .               pe   .     .
ue   erection of temporary housing      10  .               .    .     .
ue   erection of temporary housing      10  .               s1   ss    6
ue   erection of temporary housing      10  .               s2   ss    6
ue   erection of temporary housing      10  .               s3   ss    6
ue   erection of temporary housing      10  .               s4   ss    6
ue   erection of temporary housing      10  .               s5   ss    6
ue   erection of temporary housing      10  .               s6   ss    6
v1   filling 1                          15  caterpillar     pe   .     .
v2   filling 2                          10  caterpillar     pe   .     .
;

/* invoke PROC CLP */
proc clp actdata=bridge schedtime=schedtime_bridge;
   schedule finish=104;
run;

/* Create Consolidated Schedule */
proc sql;
   create table bridge_info as
          select distinct _ACTIVITY_ as ACTIVITY format $3. length 3,
          _DESC_ as DESCRIPTION, _RESOURCE_ as RESOURCE from bridge;

proc sort data=schedtime_bridge;
   by ACTIVITY;
run;

data schedtime_bridge;
   merge bridge_info schedtime_bridge;
   by ACTIVITY;
run;

proc sort data=schedtime_bridge;
   by START FINISH;
run;

proc print data=schedtime_bridge noobs width=min;;
   title 'Bridge Construction Schedule';
run;

/* start to prepare a schedule for PROC GANTT */

/* extract the project data set with ONLY precedence constraints */
data bridge_c;
   drop _lag_;
   set bridge(where=(_lag_=''));
run;

/***************************************************************/
/*        Define Utility Macros for Graphical Output           */
/***************************************************************/
/* %colorIdex creates resource-pattern-color mapping */
/* %fnLegend uses footnote to add legends according to the mapping */
/* %setPatterns executes pattern statements according to the mapping */

%macro colorIdex(res_var=, proj=, palette=, out=);
proc sql;
   create table &out as
      select distinct(&res_var) from &proj;
quit;
data &out;
   set &out;
   drop name_tr color_ct;
   format color $10.;
   retain _pattern 1;
   retain color_ct 1;
   name_tr=lowcase(strip(&res_var));
   if name_tr eq 'n/a' or name_tr eq '' then color='red';
   else do;
      color=scan(&palette, color_ct);
      color_ct=color_ct+1;
   end;
   output;
   _pattern=_pattern+1;
run;
%mend colorIdex;

/* macro %fnLegend uses footnote to add legends */
/*                                Title                                  */
/*                                                                       */
/*       color bar 11 text         ...       color bar 1n text           */
/*       color bar m1 text         ...       color bar mn text           */
%macro fnLegend(tfact=1.75,h=10,xStart=5,rhs=100,nCol=,nRow=,
                title='Resource Used',map=,font='Albany AMT',var=_RESOURCE_);
%let rH = %sysevalf(&h/(&nRow+2)); /* row height */
%let cW = %sysevalf((&rhs-2*&xStart)/&nCol); /* column width */
%let rowsH = %sysevalf(&rH*&nRow); /* height of all legend rows */
%let barH = %sysevalf(&rH*0.35); /* height of legend (colored bars) */
%let rTextH = %sysevalf(&barH*&tfact); /* height of text in legend rows */
%let tiTextH = %sysevalf(&rTextH*1.2); /* height of text in title row */

footnote;

data _null_;
   call execute('footnote1 f='||"&font"||' j=c h='||put(&tiTextH.,best.)||
                'pct '||'"'||&title||'";');
   call execute('footnote2 f='||"&font"||' h='||put(&rH.,best.)||'pct " ";');
run;

data _null_;
   set &map end=finish;
   retain count 0;

   idxRow = int(count/&nCol);
   idxCol = count - idxRow*&nCol;
   x = &xStart + idxCol*&cW;
   y = &rowsH - idxRow*&rH;

   _fnlStr_=strip(&var);
   if _fnlStr_ eq '' then _fnlStr_='N/A';
   if count eq 0 then
      call execute('footnote3 h='||put(&rowsH.,best.)||'pct " "');
   if lowcase(strip(color)) eq 'red' then
      call execute(catx(' ','f=',"&font",'h=',&barH,'pct', 'm=(',x,',',y,
                        ') pct','c=',color,
                        'box=1 bs=0 pct "                         "'));
   else
      call execute(catx(' ','f=',"&font",'h=',&barH,'pct', 'm=(',x,',',y,
                        ') pct','c=',color,'bc=',color,
                        'box=1 bs=0 pct "                         "'));
   call execute(catx(' ','f=',"&font",'h=',&rTextH,'pct', 'm=(+0.5,',y,
                     ') pct','c=black')||' " '||_fnlStr_||'"');
   count=count+1;
   if finish then call execute(';');
run;
%mend fnLegend;

%macro setPatterns(map);
data _null_;
   set ↦
   cstr=lowcase(strip(color));
   if cstr eq 'red' then
      call execute('pattern'||strip(put(_pattern,best.))||' v=e color='||
                   cstr||' repeat=1;');
   else
      call execute('pattern'||strip(put(_pattern,best.))||' v=s color='||
                   cstr||' repeat=1;');
run;
%mend setPatterns;
/***************************************************************/
/*                End Macro Definitions                        */
/***************************************************************/

/* restore precedence information, add pattern */
%let _palette='blue yellow orange cyan brown black gray green';
%colorIdex(res_var=_RESOURCE_,proj=bridge,palette=&_palette,
           out=res_col_patn);
proc sql;
   create table sched_gantt_ex1 as
      select a._SUCCESSOR_ as SUCCESSOR, b.*, c._pattern
      from bridge_c a, schedtime_bridge b, res_col_patn c
      where a._ACTIVITY_ = b.ACTIVITY and a._RESOURCE_=c._RESOURCE_;
quit;

/* draw the Gantt chart */
%setPatterns(res_col_patn);
title1 j=c h=3pct 'PROC CLP - Solution to Bridge Problem:104 days (optimal)';
%fnLegend(xStart=10,h=12,map=res_col_patn,nCol=4,nRow=2,rhs=110);


proc gantt data=sched_gantt_ex1;
   id activity start finish;
   label activity='Act';
   label start='Start';
   label finish='Finish';
   chart /
      ref=0 to 110 by 10, 100 to 105 by 1
      dur=duration
      cprec=gray
      es=start       /* start time */
      ef=finish      /* finish time */
      mindate=0
      maxdate=105
      increment=5
      scale=2.8
      height=2.7
      between=1
      skip=2
      compress      /* compresses chart onto a single page */
      nojobnum
      nolegend
      font='Albany AMT'
      act=activity
      succ=successor
      ;
run;

/* start to output a net diagram for the bridge project */

proc netdraw data=bridge_c out=bridge1 nodisplay;
   actnet / act = _activity_ succ = _successor_;
run;


proc sql;
   create table act2zone as
      select distinct _activity_, max(2, min(9,_y_)) as _zone_
      from bridge1
      where _pattern=1;
   create table bridge_zones as
      select a._zone_, b.*
      from act2zone a, bridge_c b
      where a._activity_=b._activity_;
quit;

pattern1 v=e c=green;
title1 j=c h=3.7pct 'Bridge Construction Project';
footnote;
proc netdraw data=bridge_zones out=bridge2;
   actnet /
      act = _activity_
      succ = _successor_
      compress
      id = ( _activity_ _desc_ _duration_  _resource_)
      nolabel
      nodefaultid
      boxwidth = 8
      xbetween = 12
      ybetween = 1
      centerid
      htext=1.2
      arrowhead=2
      separatearcs
      zone=_zone_
      nozonelabel
      ;
   run;