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 )