06-07-2017 07:42 AM - last edited on 06-07-2017 07:54 AM by ChrisHemedinger
What is the most efficient way to connect SAS with VBA.
I want to run my SAS programs through VBA but dont have enough knowledge of SAS and its server.
Can anyone Please help me with the problem.
06-07-2017 07:53 AM
If you want to automate SAS Enterprise Guide, check out:
If you want to build your own custom app that connects directly to SAS, check out:
06-15-2017 02:43 AM
I read you blog on Run programs in a batch and have been trying for days but no success, it always ends in error.
I am not able to understand the code properly such as objectmanager, objectfactory etc.
what should I do in such case as I want to connect SAS with VBA but not able to do it this way.
Can Someone help me..??
06-15-2017 05:41 AM
I wrote a paper a few years ago showing how you can call SAS from Excel (and turn those calls into custom Excel functions or add-ins) which you might find helpful - it's available here -> http://support.sas.com/resources/papers/proceedings14/1598-2014.pdf
By the way I learnt pretty much all I know about connecting to SAS from VBA from "the other Chris's" papers and articles
06-20-2017 07:59 AM
Ya his article has almost everything needed to connect SAS with VBA but as I am a beginner in SAS it very difficicult for me to understand the code, although I have read a lot about SAS Integration Technology but still not successful.
I have a built a stored process macro and so I am trying to call that stored process to excel through VBA.
I found some code like this but everytime I try to run this code by changing the required values it ends up in error.
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.MachineDNSName = "Machine.Company.com" obServerDef.Protocol = ProtocolBridge obServerDef.Port = 8591 ' 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
I am not able to understand where the problem is.
Are the values provided by me are wrong or there is some technical thing in this code which I dont know.
The code errors in this line >
Set obSAS = obObjectFactory.CreateObjectByServer("MyName", True, obServerDef, "username", "password")
I have attached the error message below.
Is there any book or something from where I can get the understanding of the code and the parameters so as to use it properly.??
06-20-2017 08:03 PM
From the error it would appear that the connection attempt to the remote machine where the Metadata Server is running has failed - this could be for a number of reasons. I would suggest that firstly you taken a look at the Metadata Server log after the connection attempt as authentication failures are usually detailed there. If there is nothing in the log then check the Port and Machine Name settings (for example is the DNS name set up correctly?).
There isn't a book covering IOM as far as I know but there are quite a lot of SAS Global Forum papers which you can access here
I must say that (no offence) but this is quite an advanced topic for someone new to SAS - it might be advantageous to pick one of the simpler examples from the papers (preferably running on a Workspace Server on a local PC) - get that running and try to follow it through and then gradually build up to running a Stored Process remotely.
06-21-2017 01:49 AM
I know its very complicated Chris but I can't do anything beacuse there is no one to help me and I got to do this but as per your advice I shifted my focus on Workspace server and I am following this paper http://www.lexjansen.com/pharmasug/2005/applicationsdevelopment/ad05.pdf.
I have tried few codes in VBA but was unsuccessful. I was hoping if you could help me a little more in this thing.
I have understood the code to some extent and I think my problem is somewhere in defining the user specific variables.
These are some examples which I tried.
Suppose In the Machine tab in SAS I have "ABC123" , company name is xyz and my username is sasguest and password is 12345. And Suppose my PC HostName is PC150 sub sas() Dim obServer As New SASWorkspaceManager.ServerDef obServer.MachineDNSName = "ABC123" Set obSAS = obWSMgr.Workspaces.CreateWorkspaceByServer("MYNAME", VisibilityProcess, obServer, "sasguest", "12345", xmlInfo) End Sub or sub sas() Dim obServer As New SASWorkspaceManager.ServerDef obServer.MachineDNSName = "ABC123.xyz.com" Set obSAS = obWSMgr.Workspaces.CreateWorkspaceByServer("MYNAME", VisibilityProcess, obServer, "sasguest", "12345", xmlInfo) End Sub or sub sas() Dim obServer As New SASWorkspaceManager.ServerDef obServer.MachineDNSName = "PC150" Set obSAS = obWSMgr.Workspaces.CreateWorkspaceByServer("MYNAME", VisibilityProcess, obServer, "sasguest", "12345", xmlInfo) End Sub or sub sas() Dim obServer As New SASWorkspaceManager.ServerDef obServer.MachineDNSName = "PC150.xyz.com" Set obSAS = obWSMgr.Workspaces.CreateWorkspaceByServer("MYNAME", VisibilityProcess, obServer, "sasguest", "12345", xmlInfo) End Sub I have tried all these but it shows error on the same line as "OBJECT REQUIRED" I have added these two references in VBA SAS: Integrated Object Model (SAS System 9.1) SASWorkspaceManager 1.1 Type Library
06-21-2017 05:43 AM
OK - firstly let's try to use IOM to start a local SAS workspace server session and run a simple piece of code.
1. You'll need the references added in your VBA project which you mentioned;
2. Create and execute this piece of code (changing c:\iomtest to a local folder where you have write permissions) and noting the underscore line extender at the end of one of the lines :
Public obSAS As SAS.Workspace
Public obWorkspaceManager As New SASWorkspaceManager.WorkspaceManager
Dim errorString As String
Dim sourcebuffer As String
Set obSAS = _ obWorkspaceManager.Workspaces.CreateWorkspaceByServer("MyWorkspaceName", _
VisibilityProcess, Nothing, "", "", errorString)
sourcebuffer = "libname iomtest 'c:\iomtest'; data iomtest.newclass;set sashelp.class;run;"
06-21-2017 07:02 AM - edited 06-21-2017 07:04 AM
This code is also not working.
It still shows an error on the same line.
I tried the code first by keeping everything constant and just changing that program code and second by giving the code my username and password also.
I also added all the reference which are available in VBA for SAS.
I tried it once by closing my sas session and running my VBA code but nothing is working.
Is it because I am using SAS on a client server or is there some tool missing in my system..??
Everytime My code errors on the same line.
I have attached the error msg also.
and Thanks for all your help, I really appreciate that.
06-21-2017 07:22 AM
I'm a bit stumped now because the code I gave you should have worked - the error you're getting is a COM error and it may be that there is something wrong with your installation of SAS IOM. At this point I think the best thing to do would be to raise a track with SAS Technical Support which you can do here
06-22-2017 01:06 AM
But if there is a problem with the SAS IOM installation then how come My SAS add in in Excel is able to set connection with SAS and call data.
I was thinking that if the SAS add in is working then there might be some other problem in VBA and not in SAS.
Please correct me where I am wrong.
06-22-2017 08:54 AM
I recommend that you use SASObjectManager instead of SASWorkspaceManager. Similar methods, but the SASWorkspaceManager is more of a legacy approach.
I don't have a VBA example handy, but here's a PowerShell example that should be simple enough to trancribe. See more details in this blog post.
$objFactory = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2 $objServerDef = New-Object -ComObject SASObjectManager.ServerDef $objServerDef.MachineDNSName = "server.mycompany.com" # SAS Workspace node $objServerDef.Port = 8591 # workspace server port $objServerDef.Protocol = 2 # 2 = IOM protocol # Class Identifier for SAS Workspace $objServerDef.ClassIdentifier = "440196d4-90f0-11d0-9f41-00a024bb830c" # create and connect to the SAS session $objSAS = $objFactory.CreateObjectByServer( "SASApp", # server name $true, $objServerDef, # used server definition for Workspace "sasdemo", # user ID "Password1" # password )
2 weeks ago
I have slightly adjusted the code in this thread to handle errors in my VBA/SAS code.
The problem is that some errors are not trapped such as Open code statement recursion detected.
On the other hand some other errors not being indicated in SAS Entreprise Guide occur, such as Format DATEMMYYP could not be loaded.
Any help would be greatly appreciated.
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim obObjectKeeper As New SASObjectManager.ObjectKeeper
Dim obServer As New SASObjectManager.ServerDef
Dim obSAS As SAS.workspace
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
obServer.MachineDNSName = "sas_server"
obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge
obServer.Port = 8591
obObjectFactory.LogEnabled = True
Application.DisplayAlerts = False
Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, "user_id", "password")
Set obLS = obSAS.LanguageService
StrSAS = FileContents2("C:\TEMP\bene+\Fakturace\bene_SAS\sas_code.txt")
' SAS code here, I use function FileContents2 to load contents into variable
' Debug.Print StrSAS
' Search the SAS log for an instance of a SAS ERROR message.
numlines = 900000
obSAS.LanguageService.FlushLogLines numlines, carriageControls, lineTypes, logLines
For Each line In logLines
logbuffer = logbuffer & line & vbCrLf
If InStr(1, logbuffer, "ERROR:") Then
' Debug.Print logbuffer
' Close connection to SAS server if process runs successfully.
If Not (obSAS Is Nothing) Then
' Display message box showing SAS ERROR message if found. Close connection to SAS server.
MsgBox "Fatal Error in Stored Process Execution " & logbuffer, vbCritical
If Not (obSAS Is Nothing) Then
Function FileContents2(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument
Dim lngFN As Long
On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents2 = Null
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents2 = Input(LOF(lngFN), #lngFN)
ErrCode = 0
ErrCode = Err.Number
If ReturnErrors Then
FileContents2 = CVErr(Err.Number)
FileContents2 = Null
2 weeks ago
I'm not entirely sure why you're code isn't trapping those errors but I would strongly recommend fixing the SAS code outside of an IOM call anyway. When calling SAS from another language I always use a test harness which attempts to emulate the call from within SAS as it's much easier to resolve issues.
In your case I think the "Format Couldn't be Loaded" error is caused by a missing FMTSEARCH option telling SAS where to look for the stored custom format and if you add that to your SAS program you should resolve that issue. The "Open Code Statement Recursion" error is impossible to give advice on without seeing the SAS code as it could be caused by a multiplicity of issues.
2 weeks ago
thanks for quick reply. I am using SAS Enterprise Guide (SAS EG) to debug the code. The problem is same (error free) code when run in SAS EG generates errors when run in VBA.
E. g. Format Couldn't be Loaded error in Visual Basic error log and vice versa: Open code statement recursion detected - not being indicated in VBA error log.
Open code statement recursion detected error is deliberate for testing purposes driven by missing apostrophe in prxmatch expression.
I tried to use options nosource nonotes to reduce log extent in VBA but again it works in SAS EG only.