Execute SAS Program via VBA

[DipeshGupta]

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.

[ChrisHemedinger · Accepted solution]

If you want to automate SAS Enterprise Guide, check out:

Using SAS Enterprise Guide to run programs in batch

If you want to build your own custom app that connects directly to SAS, check out:

Build your own SAS client app with Microsoft .NET

For scripting examples that use Windows PowerShell, look at this series of blog articles.

And this from @ChrisBrooks:

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

[DipeshGupta]

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..??

[ChrisBrooks]

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 

[DipeshGupta]

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.??

[ChrisBrooks]

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.

[DipeshGupta]

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 

[ChrisBrooks]

Dipesh

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

Sub sas()

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;"

obSAS.LanguageService.Submit sourcebuffer

End Sub

3. You'll notice that in the previous code there is no server definition - this is because we're going to try to run the code locally so we don't need or want to confuse things at this stage by defining a server. After you've run this there should be a data set called newclas in the folder we defined as iomtest.

4. In effect what has happened is that within the VBA code we've created a background SAS session represented by obSAS and using the Language Service we can run SAS code just as we would from the program editor.

5. Let me know if this works on your PC and then assuming it does we'll move onto the next step of connecting to a remote server.

I should admit at this point that I don't currently have access to a SAS installation to check any of this but I've done quite a bit of connecting to SAS using IOM over the years so I think we should get this working assuming you have all the necessary software.

Chris

[DipeshGupta]

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. 🙂

[ChrisBrooks]

Dipesh

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

Chris

[DipeshGupta]

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.

[ChrisHemedinger]

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
                )

[nidhishnagpal0]

Hi @ChrisHemedinger , Is it possible to run entire Process flow of SAS EG using VBA. We are trying to schedule SAS projects(with each project comprises of Multiple code,few import tasks and copy tasks - mostly to copy the logs/output at the lan location) using VBA or any other technology windows based technology.

Thanks

Nidhish

[ChrisHemedinger]

Yes @nidhishnagpal0  - see this library article for techniques and examples:

https://communities.sas.com/t5/SAS-Communities-Library/Doing-More-with-SAS-Enterprise-Guide-Automati...

[Petr_D]

Hello,

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.

Petr

Sub test()
         
 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

    obLS.Submit 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

               GoTo ErrHandler

         End If

        Next line

' Close connection to SAS server if process runs successfully.
GoodEnd:

    If Not (obSAS Is Nothing) Then

        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 " & logbuffer, vbCritical
   
    If Not (obSAS Is Nothing) Then
   
        obSAS.Close

    End If
   
      

End Sub

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
    Else
        lngFN = FreeFile()
        Open FileSpec For Input As #lngFN
        FileContents2 = Input(LOF(lngFN), #lngFN)
    End If
    ErrCode = 0
    GoTo Exit_FileContents
Err_FileContents:
    ErrCode = Err.Number
    If ReturnErrors Then
        FileContents2 = CVErr(Err.Number)
    Else
        FileContents2 = Null
    End If
    Err.Clear
Exit_FileContents:
    Close #lngFN
End Function