BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DipeshGupta
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

17 REPLIES 17
ChrisHemedinger
Community Manager

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

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
DipeshGupta
Calcite | Level 5

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
Ammonite | Level 13

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

DipeshGupta
Calcite | Level 5

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

 

 

 


Capture.PNG
ChrisBrooks
Ammonite | Level 13

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
Calcite | Level 5

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
Ammonite | Level 13

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
Calcite | Level 5

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


Capture.PNG
ChrisBrooks
Ammonite | Level 13

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
Calcite | Level 5

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
Community Manager

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
                )

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
nidhishnagpal0
Calcite | Level 5

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Petr_D
Calcite | Level 5

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 16183 views
  • 0 likes
  • 5 in conversation