Desktop productivity for business analysts and programmers

Calling SAS through VBA

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Calling SAS through VBA

Hello,

I'm running SAS Enterprise Guide 5.1 on an external server and I want to call SAS commands through VBA.

I'm really not knowledgeable in this area so I tried to use a code I found (below). I sort of get what it's doing but clearly not enough.

When I run the code I get the error below.

Wondering if anyone has had any experience with this and any help/guidance would be much appreciated!

Also I have the SAS references ticked in VBA as well as Microsoft ActiveXData Objects.

Thank you.

Emre

Error:

     Run-time error '-2147213310 (80042002)';

          <connectionAttempts>

          <connectionAttempts>

               <sasserver></sasserver>    

               <sasmachinednsname></sasmachinednsname>    

               <sasport>0</sasport>    

               <saslogin></saslogin>    

               <status>0x80040154</status>

Code  (highlighted is the problem code):

     Dim obWS As SAS.Workspace

     Dim obWSM As New SASWorkspaceManager.WorkspaceManager

     Sub Form_Load()

     Dim obConn As New ADODB.Connection

     Dim obRS As New ADODB.Recordset

     Dim errorString As String

     Rem start the SAS session

     Set obWS = obWSM.Workspaces.CreateWorkspaceByServer("My", _

           VisibilityProcess, Nothing, "", "", errorString)

     Rem submit some SAS code

     obWS.LanguageService.Submit _

          "PROC IMPORT OUT=PATTERNS_NEW DATAFILE = ""\\Gblontfs01\test.xlsm"" DBMS = EXCELCS REPLACE; SHEET= Export; RUN; PROC APPEND BASE=EO.PATTERNS DATA=PATTERNS_NEW FORCE; RUN;"

     Rem open an ADO connection to the data set

     Dim connString As String

     connString = "provider=sas.iomprovider.1; SAS Workspace ID=" _

             + obWS.UniqueIdentifier

     obConn.Open connString

     obRS.Open "work.a", obConn, adOpenStatic, adLockReadOnly, _

          adCmdTableDirect

     Rem write the table as html (visible in Word with tags)

     obRS.MoveFirst

     Dim sTable As String

     sTable = "<TABLE BORDER=0><TBODY><TR><TD class="Data">"

     Selection.TypeText sTable

     sTable = obRS.GetString(, , "</TD><TD class="Data">", _

     "</TD></TR><TR><TD class="Data">")

     Selection.TypeText sTable

     sTable = "</TD></TR></TBODY></TABLE>"

     Selection.TypeText sTable

     Rem tidy-up

     obRS.Close

     obConn.Close

     obWS.Close

     End Sub


Accepted Solutions
Solution
‎03-25-2013 05:41 AM
Frequent Contributor
Posts: 118

Re: Calling SAS through VBA

Well the message says everything: you are connecting to the metadata server and not the workspaceserver.

The workspaceservers default port is 8591.

Perhaps the workspaceserver also runs on a different machine. Connect to your profile, go to the serverlist, locate the server you want to connect to and right click the context menu "properties" to see the right connection profile for your workspaceserver.

BTW: if you are using IWA (integrated windows authentication) you must not provide a username and password.

So i suggest this code:

obServer.MachineDNSName = "GBLONTAS68"

obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge

obServer.Port = 8591

Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, Nothing, Nothing)

View solution in original post


All Replies
Community Manager
Posts: 2,707

Re: Calling SAS through VBA

I don't have a VBA-ready example, but I do have some examples in Windows PowerShell.  The syntax is different, but the same objects are used.

I suggest using SAS Object Manager (instead of Workspace Manager, which is a bit older and supported only as legacy).

See this post:

Using Windows PowerShell to connect to a SAS Workspace server - The SAS Dummy

And also this sasCommunity page:

http://www.sascommunity.org/wiki/Create_Your_Own_Client_Apps_Using_SAS_Integration_Technologies

Chris

Frequent Contributor
Posts: 118

Re: Calling SAS through VBA

Hi,

you said you run EG and SAS on a remote server. So i guess that you don't have a local SAS installation.

When you call the function CreateWorkspaceByServer you should pass a reference to a ServerDef object as the third parameter. In this ServerDef object you define where your remote SAS Server is located (DNS-name, port, protocoll, ...).

You can retrieve most of that information that you need for the connection from the Enterprise Guide "Server List" by right clicking the server and selecting the context menu "properties".

By the way: can somebody at SAS please update the SAS/IT Client documentation?

I was not aware that the Workspace Manager was deprecated and seen as legacy. I just wasted half a day yesterday with some weird COM Exceptions when i tried to create a ServerDef object in a 64bit environment...

Andreas

Trusted Advisor
Posts: 1,061

Re: Calling SAS through VBA

Hi, Andreas

When I find problems in the documentation, I submit them to tech. support. Up till now, they've accepted them with good grace.

Tom

Occasional Contributor
Posts: 17

Re: Calling SAS through VBA

Hey guys,

Thanks very much for the help and sorry for the late reply.

I'm trying this code...

Sub Form_Load()

Dim obObjectFactory As New SASObjectManager.ObjectFactory

Dim obObjectKeeper As New SASObjectManager.ObjectKeeper

Dim obServer As New SASObjectManager.ServerDef

Dim obSAS As SAS.Workspace

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

obServer.MachineDNSName = "GBLONTAS68"

obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge

obServer.Port = 8561

obObjectFactory.LogEnabled = True

Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, "myusername", "mypassword")

obSAS.LanguageService.Submit ("DATA EO.VBATEST; SET EO.FX; RUN;")

End Sub

...and getting this error.

VBA ERROR.JPG

server.JPG

Any thoughts on how I can solve this?

Thank you ever so much again!

Solution
‎03-25-2013 05:41 AM
Frequent Contributor
Posts: 118

Re: Calling SAS through VBA

Well the message says everything: you are connecting to the metadata server and not the workspaceserver.

The workspaceservers default port is 8591.

Perhaps the workspaceserver also runs on a different machine. Connect to your profile, go to the serverlist, locate the server you want to connect to and right click the context menu "properties" to see the right connection profile for your workspaceserver.

BTW: if you are using IWA (integrated windows authentication) you must not provide a username and password.

So i suggest this code:

obServer.MachineDNSName = "GBLONTAS68"

obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge

obServer.Port = 8591

Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, Nothing, Nothing)

Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Calling SAS through VBA

Can you guide me. Its not working for me. I am getting Automation error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147213310 (80042002)':

Automation error
---------------------------
OK Help
---------------------------
Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Calling SAS through VBA

Can you guide me how to execute block of code or say doing joins ?
Occasional Contributor
Posts: 17

Re: Calling SAS through VBA

Thanks very much guys. Works perfectly!

Regular Learner
Posts: 1

Re: Calling SAS through VBA

Hi,

I am newbie in SAS. I am trying to run a sas code via VBA after setting up SAS Reference in VBA. However i am getting an error massage "Active X component Cann't create  object". please note i am using BASE SAS 9.3 and windows 7- 64 bit and excel 64 bit too.

for me its seems like, VBA is unable to communicate with SAS and its unable to create a workspace object at first place.

 

I have tried my best but didn't get any concrete answer through google. Expert please advise and help me.

 

Thanks a lot in advance for giving your valuable time to look into this.

 

 

Valued Guide
Posts: 2,111

Re: Calling SAS through VBA

Please post your question as a new one.  This one was marked 'answered' so many people won't look at it further.

Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Calling SAS through VBA

Hi feyzi -
Can you help me with sample code. i am not able to get this. getting an auotmation error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147213310 (80042002)':

Automation error
---------------------------
OK Help
---------------------------
Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Calling SAS through VBA

even i get this error. Do you have solution

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147467259 (80004005)':

Automation error
Unspecified error
---------------------------
OK Help
---------------------------
Community Manager
Posts: 2,707

Re: Calling SAS through VBA

The error codes that you've shared are COM errors: caused when VBA cannot properly use the components that you've referenced.

I suggest that you either create a new topic and supply the code you are trying to use, plus some details of your installation -- or perhaps work with SAS Technical Support to see what's going on with your setup.

This topic already has a solution, so this isn't the best place to post your query.
Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Calling SAS through VBA

Chris -
Thanks for the feedback. Can you suggest me a solved post., where can I refer this. Let me explain you again what I am trying to -
I want to build a connection using VBA and get data in to spreadsheet. The SAS is present in one of my server and I will be executing the macro on my local. Above was the code what I have mentioned. The error is prompted only when I try to open the recordset connecion.

Cheers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 8878 views
  • 7 likes
  • 7 in conversation