BookmarkSubscribeRSS Feed
Greg
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....

BR

Greg
1 REPLY 1
Vince_SAS
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
http://www2.sas.com/proceedings/sugi27/p174-27.pdf

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:
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/stpwebapp.html

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

Vince
SAS R&D

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!

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.

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
  • 1 reply
  • 885 views
  • 0 likes
  • 2 in conversation