BookmarkSubscribeRSS Feed
Calcite | Level 5
we are trying to use sas Stored Processes from VBA with IOM interface:
It seems to be ok but the password stored in metadata server is propagated and we don't want to store password in SAS Metadata.

Here is an example :
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim spManager As New SASStoredProcessService.Manager
Dim spInstance As SASStoredProcessService.Instance
Dim spExecution As SASStoredProcessService.Execution
Dim spReader As SASStoredProcessService.StreamReader
obObjectFactory.SetMetadataFile "C:\Test\VBA\oms_serverinfo.xml", "", False

....and so on

3 questions :
-where is the complete documentation ?
-is IOM the best way to execute STP from VBA ?
-If yes, it is possible to force IOM objets to use the password write in the configuration file ? For the moment this password is used only for connection to metadata server. After that,the wrong password stored in metadata is propagated....


Rhodochrosite | Level 12
The experimental COM API is used internally by SAS applications -- you can use it at your own risk. In SAS 9.2 we plan to formally release a .NET API that will be documented and supported.

One thing you can do with VBA is try the techniques described in this paper:

Techniques for SAS-Enabling Microsoft Office in a Cross-Platform Environment

Some of the information is dated, but you may find the section on VBA helpful. The paper uses SAS/IntrNet as an example, but it can be extended to SAS Stored Processes because they are basically "The Son of SAS/IntrNet"(tm).

For example, this Excel macro, adapted from the paper (only changed the URL), runs the "Hello World" stored process sample and inserts the output into the current worksheet.
Sub insertHTML()
Dim currentCell As Range
Dim tempWorkbook As Workbook
Dim tempRange As Range
Set currentCell = Application.Workbooks(ActiveWorkbook.Name).ActiveSheet.Application.ActiveCell
Set tempWorkbook = Workbooks.Open("http://your-server:your-port/SASStoredProcess/do?_PROGRAM=/Samples/Stored Processes/Sample: Hello World")
Set tempRange = tempWorkbook.ActiveSheet.UsedRange
tempRange.Copy currentCell
tempWorkbook.Close False
End Sub
You will get prompted for a username and password in order to run the stored process. It look like Excel caches this information, so you should only have to enter it for the first stored process that you execute in a given workbook.

Documentation for the SAS Stored Process Web Application is at:

Excel Web Queries, also discussed in the paper, are something else you should probably evaluate.




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation