Sample VBA macro to run a stored process

The following macro invokes a stored SAS process and passes a character string (the name of a SAS data set) to it. The SAS stored process outputs the data set as an HTML file. You can paste the following sample into the Visual Basic Editor in ArcMap, but replace the code in bold with code that is appropriate for you.

VBA Macro code:


Sub passDSName()
  Rem Start the SAS server
  Dim SASws As SAS.Workspace
  Dim SASwsm As New SASWorkspaceManager.WorkspaceManager
  Dim strError As String
  Set SASws = SASwsm.workspaces.CreateWorkspaceByServer _
    ("MySAS", VisibilityProcess, Nothing, "", "", strError)

  Rem Run the stored process
  Dim SASproc As SAS.StoredProcessService
  Set SASproc = SASws.LanguageService.StoredProcessService
  SASproc.Repository = "file:c:\sas"
  SASproc.Execute "MyCode.sas", "ds=Sasuser.Export_output"

  Rem Shut down the SAS server
  SASwsm.workspaces.RemoveWorkspaceByUUID SASws.UniqueIdentifier
  SASws.Close
  Set SASws = Nothing
End Sub

Note: Replace the code in bold with code that is appropriate for you:

c:\sas	The directory in which your SAS stored process is located
MyCode.sas	The filename of your SAS stored process (you can omit ".sas" when the extension is .sas)
ds	The parameter name. This name is arbitrary but must match the parameter name in your SAS stored process.
Sasuser.Export_output	The character string being passed to the SAS stored process (in this case, the name of a SAS data set)

SAS Code

For this sample, the following SAS code is saved as c:/sas/MyCode.sas. It receives a parameter named ds and outputs a file as c:/sas/MySASdsOut.html.


/* The paramater names must match in the VB code and in the SAS code. */
/* *Processbody; is required */
%let ds=;
*Processbody;
%stpbegin;
ods html file="c:/sas/MySASdsOut.html";
proc contents data=&ds;
run;
ods html close;
%stpend;

Note: Replace the code in bold with code appropriate for you:

ds	The parameter name. This name is arbitrary but must match the parameter name in your Visual Basic macro.
c:/sas/MySASdsOut.html	The complete path and filename of the output file to be created.