Desktop productivity for business analysts and programmers

Execute SAS Program via VBA

Reply
Contributor
Posts: 40

Execute SAS Program via VBA

[ Edited ]

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.

Community Manager
Posts: 2,693

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

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

Contributor
Posts: 40

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

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

Frequent Contributor
Posts: 117

Re: Execute SAS Program via VBA

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 Smiley LOL

Contributor
Posts: 40

Re: Execute SAS Program via VBA

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

 

 

 

Attachment
Frequent Contributor
Posts: 117

Re: Execute SAS Program via VBA

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.

Contributor
Posts: 40

Re: Execute SAS Program via VBA

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 
Frequent Contributor
Posts: 117

Re: Execute SAS Program via VBA

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
Contributor
Posts: 40

Re: Execute SAS Program via VBA

[ Edited ]

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. Smiley Happy

Attachment
Frequent Contributor
Posts: 117

Re: Execute SAS Program via VBA

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

Contributor
Posts: 40

Re: Execute SAS Program via VBA

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.

Community Manager
Posts: 2,693

Re: Execute SAS Program via VBA

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
                )

 

 

Ask a Question
Discussion stats
  • 11 replies
  • 260 views
  • 0 likes
  • 3 in conversation