Here's some code for calling a Stored Process from VBA using SAS IOM ' Ensure all variables are declared before use. Option Explicit ' Initialise SAS Workspace objects. Public obSAS As SAS.Workspace Public obWorkspaceManager As New SASWorkspaceManager.WorkspaceManager Private Sub runBtn_Click() ' Initialise objects for use in connection. Dim errorString As String Dim sourcebuffer As String Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obObjectKeeper As New SASObjectManager.ObjectKeeper Dim obServerDef As New SASObjectManager.ServerDef Dim obStoredProcessService As SAS.StoredProcessService Dim logLines() As String Dim carriageControls() As SAS.LanguageServiceCarriageControl Dim lineTypes() As SAS.LanguageServiceLineType Dim line As Variant Dim logbuffer As String Dim numlines As Long ' Define SAS connection details. obServerDef.Port = 8591 obServerDef.Protocol = SASWorkspaceManager.Protocols.ProtocolBridge obServerDef.MachineDNSName = "<dnsname>" ' Create a new SAS workspace instance on remote SAS Workspace server. Set obSAS = obObjectFactory.CreateObjectByServer("myName", True, obServerDef, "<username>", "<password>") Call obObjectKeeper.AddObject(1, "WorkspaceObject", obSAS) ' Assign a dummy location to the SAS _WEBOUT file [IMPORTANT STEP]. sourcebuffer = "filename _WEBOUT dummy;" obSAS.LanguageService.Submit sourcebuffer ' Call the 'test_iom' SAS Stored Process. Set obStoredProcessService = obSAS.LanguageService.StoredProcessService obStoredProcessService.Repository = "file:" & "StoredProcesses/admin/testing" obStoredProcessService.Execute "test_iom", "" ' Search the SAS log for an instance of a SAS ERROR message. numlines = 2000 obSAS.LanguageService.FlushLogLines numlines, carriageControls, lineTypes, logLines For Each line In logLines logbuffer = logbuffer & line & vbCrLf If InStr(1, logbuffer, "ERROR:") Then GoTo ErrHandler End If Next line ' Close connection to SAS server if process runs successfully. GoodEnd: If Not (obSAS Is Nothing) Then obObjectKeeper.RemoveObject obSAS obSAS.Close End If Exit Sub ' Display message box showing SAS ERROR message if found. Close connection to SAS server. ErrHandler: MsgBox "Fatal Error in Stored Process Execution " & line If Not (obSAS Is Nothing) Then Call obObjectKeeper.RemoveAllObjects Call obSAS.Close End If End Sub Obviously you will need to replace the values between angled brackets <> with appropriate ones for your installation. When it comes to getting the output into Excel I'd use ADO - there are quite a few examples on the web, not least two in my paper from this years SAS Global Forum.... 🙂 http://support.sas.com/resources/papers/proceedings14/1598-2014.pdf Chris
... View more
