The Microsoft Project Conversion Macros

Example 4.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 Figure 4.5. The tasks and their precedence relationships are the same as in Example 4.1, but there are two custom fields: "Flag1" and "Text1" ("Text1" is renamed as "Department Num.")



msptosas2.gif (91564 bytes)

Figure 4.5: 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 Figure 4.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 Figure 4.6.



msptosas21.gif (77911 bytes)

Figure 4.6: Merged Data Set

Note that a custom field is extracted and saved in the data set Task_Attributes only when the field has nondefault values for at least one task or when it has been renamed in Microsoft Project. If you are not sure whether or not Flag1 is present in the data set Task_attributes, you can use the following SAS statements instead:

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

Previous Page | Next Page | Top of Page