topic Re: Execute SAS Program via VBA in SAS Enterprise Guide

Execute SAS Program via VBA

DipeshGupta — Wed, 07 Jun 2017 11:54:36 GMT

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.

Re: Execute SAS Program via VBA

ChrisHemedinger — Tue, 14 May 2019 11:54:37 GMT

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

Re: Execute SAS Program via VBA ('LibGit2Sharp.Core.NativeMethods' threw an exception)

DipeshGupta — Thu, 15 Jun 2017 06:43:04 GMT

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

Re: Execute SAS Program via VBA

ChrisBrooks — Thu, 15 Jun 2017 09:41:37 GMT

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

Re: Execute SAS Program via VBA

DipeshGupta — Tue, 20 Jun 2017 11:59:28 GMT

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

Re: Execute SAS Program via VBA

ChrisBrooks — Wed, 21 Jun 2017 00:03:18 GMT

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.

Re: Execute SAS Program via VBA

DipeshGupta — Wed, 21 Jun 2017 05:49:03 GMT

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

Re: Execute SAS Program via VBA

ChrisBrooks — Wed, 21 Jun 2017 09:43:28 GMT

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

Re: Execute SAS Program via VBA

DipeshGupta — Wed, 21 Jun 2017 11:04:03 GMT

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

Re: Execute SAS Program via VBA

ChrisBrooks — Wed, 21 Jun 2017 11:22:09 GMT

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

Re: Execute SAS Program via VBA

DipeshGupta — Thu, 22 Jun 2017 05:06:33 GMT

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.

Re: Execute SAS Program via VBA

ChrisHemedinger — Thu, 22 Jun 2017 12:54:36 GMT

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
                )

Re: Execute SAS Program via VBA

Petr_D — Tue, 06 Feb 2018 10:27:39 GMT

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

Re: Execute SAS Program via VBA

ChrisBrooks — Wed, 07 Feb 2018 00:36:53 GMT

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.

Re: Execute SAS Program via VBA

Petr_D — Wed, 07 Feb 2018 14:18:37 GMT

Hello,

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.

Petr

Re: Execute SAS Program via VBA

ChrisHemedinger — Wed, 07 Feb 2018 15:54:13 GMT

You might need to add options nofmterr to your code to prevent errors when a SAS format can't be found. EG submits that statement automatically.

Re: Execute SAS Program via VBA

nidhishnagpal0 — Tue, 14 May 2019 09:47:58 GMT

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

Re: Execute SAS Program via VBA

ChrisHemedinger — Tue, 14 May 2019 11:42:30 GMT

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

https://communities.sas.com/t5/SAS-Communities-Library/Doing-More-with-SAS-Enterprise-Guide-Automation/ta-p/417832