Example 6.9 Round Trip between a SAS Program and Microsoft Project

This example demonstrates how to convert a Microsoft Project (MSP) database file into SAS by using the %MSPTOSAS macro, and then to convert the file back to MSP by using the %SASTOMSP macro.

Output 6.9.1 MS Project Window

Suppose you want to convert the MSP project shown in Output 6.9.1 into SAS software. You can convert the corresponding MDB file into a SAS data set by using the %MSPTOSAS macro, as follows:

   %msptosas(mdbfile=C:\MSPROJ\mspsas4.mdb, library=C:\MSPROJ, 
             version=2003)  

The %MSPTOSAS macro generates the data sets Activity, Calendar, Holiday, Workday, Resource, Schedule, Task_Attributes, and Prefs, as well as the file callpm.sas. The following SAS statements can be found either in the callpm.sas file or in the SAS log:

libname mspout "C:\MSPROJ";
PROC PM data = mspout.activity project=mspout.prefs
     caledata = mspout.calendar
     workdata = mspout.workday
     out=mspout.schedule   
     interval=dtday
     date="17DEC06:08:00:00"dt
     daylength=" 8:00"t
     suppressobswarn
     setfinishmilestone;
activity ACTID;
successor SUCCUID / LAG = LAG;
duration DURATION;
project PNTUID;
id ACTIVITY ACTUID;
RUN;

Output 6.9.2 shows the resulting PM window.

Output 6.9.2 PM Window

By taking the parameters from the preceding PM invocation, you can specify the values in the following %SASTOMSP call. The %SASTOMSP macro converts the project back into Microsoft Project format.

filename mspref "C:\MSPROJ\sasmsp5.mdb";
%sastomsp(library=C:\MSPROJ, mdbfile=mspref,
          actds=activity, calds=calendar, workds=workday,
          scheduleds=schedule, _interval=dtday,
          _date="17DEC06:08:00:00"dt, _daylength=" 8:00"t,
          _activity=ACTUID, _successor=SUCCUID,
          _lag=LAG, _dur=DURATION, _project=PNTUID,
          _id=ACTIVITY ACTUID)

In this example, after the project was converted to SAS code, the same project was converted back to Microsoft Project. The round trip between Microsoft Project and SAS software enables you to harness the power of SAS programming in scheduling, resource leveling, data processing, and more. Once your calculations are complete, you can return the results to Microsoft Project. Note that because SAS/OR Project Management and Microsoft Project use different strategies in calculating schedules, your project might show different start or finish times in SAS software and Microsoft Project. To compare the differences, you can display SAS schedules both in a table column view and in a Gantt chart, as described in Example 6.8.