![]() | ![]() | ![]() |
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:
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
From SAS Server/Binder. Select Libraries
SASHELP
Class. Click on the button labeled Open.
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.
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.
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.
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.
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 Project as
Local computer. In the Save As window, save the project to C:\Documents and Settings\EG\Project.egp.
Exit.
Programs
Accessories
Notepad.
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
|
Save, save the code to C:\Documents and Settings\EG\EG3VBScript.vbs.
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.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.
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.
| Type: | Sample |
| 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 |



