Example 6.2 Importing Activity Attributes

This example demonstrates the ability of the %MSPTOSAS macro to import Microsoft Project custom fields to a SAS data set. Consider the Microsoft Project window displayed in Output 6.2.1. The tasks and their precedence relationships are the same as in Example 6.1, but the version of the Microsoft Project is 2003 and there are two custom fields: "Flag1" and "Text1" ("Text1" is renamed as "Department Num.")

Output 6.2.1 Microsoft Project Window

To convert this project, use the following SAS macro call:

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

After the conversion, the PM window is the same as in Output 6.1.2. However, in the resulting data set Task_Attributes there are two more variables: Flag1 and Text1 (Text1 has the label "Department Num."). Note that the three data sets Task_Attributes, Activity, and Schedule have two variables in common: ACTUID and ACTIVITY. ACTUID is numeric; ACTIVITY is character. Different activities can have identical ACTIVITY values, but each activity has a unique ACTUID value. Hence we recommend using ACTUID to identify the activities. For example, if you want to create a table consisting of the variables ACTIVITY and DURATION from the Activity data set, E_START and E_FINISH from the Schedule data set, and Flag1 and Text1 from the Task_Attributes data set, you can use the following SAS statements:

proc sql;
   create table merged as
      select a.ACTIVITY, a.DURATION,
         b.E_START, b.E_FINISH, c.Flag1, c.Text1
      from mspout.Activity a, mspout.Schedule b,
         mspout.Task_attributes c
      where a.ACTUID=b.ACTUID=c.ACTUID 
         and a.ACTIVITY is not missing;
quit;

These statements merge variables from individual data sets. The variable ACTUID is used to identify activities; a.ACTIVITY is not missing is used to skip the logic observations.

The resulting data set Merged appears in Output 6.2.2.

Output 6.2.2 Merged Data Set

%macro merge;
/* Open the data set mspout.Task_attributes and
   return an identifier &dsid. */
%let dsid=%sysfunc(open(mspout.Task_attributes));
proc sql;
    create table merged as
    select a.ACTIVITY, a.DURATION,
        b.E_START, b.E_FINISH,
        %if %sysfunc(varnum(&dsid,Flag1)) %then %do;
            c.Flag1,
        %end;
        c.Text1
    from mspout.Activity a, mspout.Schedule b,
        mspout.Task_attributes c
    where a.ACTUID=b.ACTUID=c.ACTUID 
        and a.ACTIVITY is not missing;
quit;
/* Close the data set opened with the identifier &dsid. */
%let rc = %sysfunc (close (&dsid));
%mend;

%merge;