25275 - How to batch process Enterprise Guide 3.0 projects that will save results as HTML, MS Excel, and SAS tables

Sample 25275: How to batch process Enterprise Guide 3.0 projects that will save results as HTML, MS Excel, and SAS tables

The built-in scheduler in SAS Enterprise Guide 3.0 works nicely for automating a project to execute all its tasks; however, in order to take full advantage of the OLE Automation capabilities in SAS Enterprise Guide, a scripting language such as VBScript is required. Additionally, some of the functionality provided in this example is only available via OLE Automation. The VBScript in this technical tip will do the following:

executes an Enterprise Guide project
saves List data task's HTML results to a specific HTML file located on the client
saves Summary task's HTML results to a specific Excel file located on the client
saves Query task's output data to a specific Excel data file located on the client
emails task's HTML results (Microsoft's security may cause Outlook to prompt)
saves Summary task's output data to a specific SAS data file located on the client
saves Summary task's output data to a specific Excel data file located on the client
saves a Graph task's HTML results to a specific HTML file located on the client.

First, download a free copy of Microsoft Windows Script 5.6 from this link:
http://msdn2.microsoft.com/en-us/library/1cw548cz.aspx

After installing Microsoft® Windows Script 5.6, follow these steps for creating an Enterprise Guide project that will be used by the VBScript code. Note: Some of these steps require write access to the SASUSER library on the SAS server. If write access to the SASUSER library is not available at your site, adjust the steps accordingly. Also, SAS Enterprise Guide is required on the machine that executes the VBScript.

Open SAS Enterprise Guide 3.0 or higher, and create a new project.
Open the data set SASHELP.CLASS by selecting File Open From SAS Server/Binder. Select Libraries SASHELP Class. Click on the button labeled Open.
Create a listing of the data by selecting Describe List Data. In the List Data window, assign Age, Height, and Weight to the role of "List variables". Click the Run button. After the results are generated, close the output window.
Create a summary statistics report by selecting Describe Summary Statistics. In the Summary Statistics window, assign Age, Height, and Weight to the role of "Analysis variables". In the Results window, click the option to 'Save statistics to data set'. Click the Run button. After the results are generated, close the output window.
With the Class data set active, create a bar chart by selecting Graph Bar Chart Simple Vertical Bar. In the Bar Chart window, assign Age to the role of "Column to chart" and Height to the role of "Sum of". Click the Run button. After the results are generated, close the output window.
With the Class data set active, create a query by selecting Data Filter and query active data. In the Filter Data window, drag over Height. From the Edit Filter Condition window, change the Operator to >= and enter 62 for Value. Click OK. From the Advanced window, click the Change button and change the output data set name to QURY1234. Click Save. Click the Run Query button. After the results are generated, close the data window.
Using SASUSER.QURY1234 as input, create a listing by selecting Describe List Data. In the List Data window, assign Age, Height, and Weight to the role of "List variables". Click the Run button. After the results are generated, close the Output window. RightMouse on the task's node that was just created, labeled 'List Data', and choose Rename. Rename the label to 'List Data Two' without the quotes.
Save the project by selecting the project node labeled 'Project', and select File Save Project as Local computer. In the Save As window, save the project to C:\Documents and Settings\EG\Project.egp.
Close Enterprise Guide by selecting File Exit.
Open Microsoft Notepad from your PC by selecting Start Programs Accessories Notepad.
Copy and paste the following VBScript code into the Notepad window.

Change the email addresses contained in the VBScript to valid values. Due to Microsoft® security settings at most sites, using the SendMail method in this VBScript will likely cause Outlook to prompt.

Option Explicit
Dim app
Call dowork
'shut down the app
If not (app Is Nothing) Then
    app.Quit
    Set app = Nothing
End If

Sub dowork()
    ' Start up Enterprise Guide using the project name
    Dim prjName
    Dim prjObject
    prjName = "C:\Documents and Settings\EG\Project.egp"
    Set app = CreateObject("SASEGObjectModel.Application")

    If Checkerror("CreateObject") = True Then
        Exit Sub
    End If

    ' open the project
    Set prjObject = app.Open(prjName,"")

    If Checkerror("app.Open") = True Then
        Exit Sub
    End If

    ' run the project
    prjObject.run

    If Checkerror("Project.run") = True Then
        Exit Sub
    End If

    ' Save the new project
    prjObject.Save

    If Checkerror("Project.Save") = True Then
        Exit Sub
    End If

    Dim objResults1
    Dim objResults2
    Dim objResults3
    Dim objResults4
    Dim objResults5
    Dim objResults6
    Dim objResults7
    Dim objResults8

    Dim objResult

    Dim objTasks
    Dim objTasks2
    Dim objProjectItems

    Set objProjectItems = prjObject.ProjectItems
    Set objTasks        = objProjectItems("CLASS").Tasks
    Set objTasks2       = objProjectItems("SASUSER.QURY1234").Tasks

    Set objResults1 = objTasks("Summary Statistics").Results
    Set objResults2 = objTasks("Summary Statistics").Results
    Set objResults3 = objTasks("Summary Statistics").OutputDatasets
    Set objResults4 = objProjectItems("Query1 for CLASS").OutputDatasets
    Set objResults5 = objTasks("List Data").Results
    Set objResults6 = objProjectItems("Query1 for CLASS").OutputDatasets
    Set objResults7 = objTasks("Bar Chart").Results
    Set objResults8 = objTasks2("List Data Two").Results

    set objResult = objResults1(0)
    ' Save the summary statistics html results as an Excel file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\Summary.xls")

    Set objResult = objResults2(0)
    ' Save the summary statistics html results as an Html file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\Summary.html")

    Set objResult = objResults3(0)
    ' Save the summary statistics output data as a SAS data file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\Summary.sas7bdat")

    Set objResult = objResults3(0)
    ' Save the summary statistics output data as an Excel file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\SummaryData.xls")

    Set objResult = objResults4(0)
    ' Save the query output data as a SAS data file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\Query.sas7bdat")

    set objResult = objResults5(0)
    ' Save the list data html results as an HTML file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\List.html")

    set objResult = objResults6(0)
    ' Save the query output data as an Excel file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\Query.xls")

    set objResult = objResults7(0)
    ' Save the graph html results as an HTML file on the client machine
    objResult.SaveAs ("C:\Documents and Settings\EG\Graph.html")

    set objResult = objResults8(0)
    ' Prepare to email the list data two html results
    objResult.SaveAs("c:\Documents and Settings\EG\ListTwo.html")

    Dim toList(2)
    Dim copyList(1)
    Dim profile
    Dim password
    Dim Subject
    Dim text
    profile=""
    password=""
    toList(0) = "first.last@company.com"
    toList(1) = "first2.last2@company.com"
    toList(2) = "first3.last3@company.com"
    copyList(0) = "first4.last4@company.com"
    copyList(1) = "first5.last5@company.com"
    subject = "This is a test for the text of the email subject."
    text = "This is a test for the text of the email body."
    WScript.Echo toList(0)
    wScript.Echo copyList(0)
    Call objResult.SendMail("", "",(toList), (copyList) , subject, text)
    ' Close the project
    prjObject.Close
    If Checkerror("Project.Close") = True Then
        Exit Sub
    End If
End Sub
Function Checkerror(fnName)
    Checkerror = False
    Dim strmsg
    Dim errNum
    If Err.Number <> 0 Then
        strmsg = "Error #" & Hex(Err.Number) & vbCrLf & "In Function " & _
                 fnName & vbCrLf & Err.Description
        MsgBox strmsg
        Checkerror = True
    End If
End Function

Using File Save, save the code to C:\Documents and Settings\EG\EG3VBScript.vbs.
Close the Notepad window.
Test the VBScript by double-clicking on the file EG3VBScript.vbs in the Microsoft Windows Explorer window.
The VBScript should have written nine files in the folder C:\Documents and Settings\EG.
Delete the nine output files that were just created in the folder C:\Documents and Settings\EG so you can schedule the task in the next step and confirm the files are created again.
To schedule EG3VBScript.vbs, open the Control Panel and select Scheduled Tasks Add Scheduled Task. In the Scheduled Task Wizard, select Next and then click on the Browse button. Navigate to C:\Documents and Settings\EG\EG3VBScript.vbs. Click on the Open button. For "Perform this task", select One time only and then select Next. Specify a Start time that is only a few minutes from the current time. Select Next. Enter your user name and password for your local machine. Select Next and then select Finish.
Check the folder C:\Documents and Settings\EG after the scheduled time to confirm that the nine files were created.

For additional documentation, download the SAS Enterprise Guide 3.0 OLE Automation Reference Help from this link: http://support.sas.com/documentation/onlinedoc/guide/

If you experience any problems with this Technical Tip, send email to my attention using: support@sas.com.

About the Author
Bill Sawyer is a senior technical support analyst at SAS and has supported SAS/Graph, SAS/Base, and since 2000, has supported SAS Enterprise Guide. Bill has written numerous technical documents, and has also developed several Microsoft® Visual Basic Add-In tasks that can be included into SAS Enterprise Guide.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Date Modified:	2008-02-07 06:39:17
Date Created:	2005-03-31 17:02:13

Product Family	Product	Host	Starting Release	Ending Release
SAS System	SAS Enterprise Guide	Microsoft Windows 2000 Advanced Server	3.0
		Microsoft Windows 2000 Datacenter Server	3.0
		Microsoft Windows 2000 Server	3.0
		Microsoft Windows 2000 Professional	3.0
		Microsoft Windows NT Workstation	3.0
		Microsoft Windows Server 2003 Datacenter Edition	3.0
		Microsoft Windows Server 2003 Enterprise Edition	3.0
		Microsoft Windows Server 2003 Standard Edition	3.0
		Microsoft Windows XP Professional	3.0

Knowledge Base

Sample 25275: How to batch process Enterprise Guide 3.0 projects that will save results as HTML, MS Excel, and SAS tables

Operating System and Release Information