Your SAS programs, embedded in web apps and elsewhere

Running Stored Process using VBA - Facing issues in passing prompt values

Reply
New Contributor
Posts: 2

Running Stored Process using VBA - Facing issues in passing prompt values

I am developing a tool in VBA which can interact with SAS and run a particular code based on user input. The SAS prompt 'Var_list' used in the stored process can accept a list of 4 variables 'Var1', 'Var2', 'Var3', 'Var4' (These values are present in cells F12:F14). I am using the following code to pass these values to the SAS prompt but my code doesn't work. Please suggest where am I going wrong

 

 

Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

Set WB = ThisWorkbook
Set BG = WB.Worksheets("Tool")


' This is how to define prompts when you add a stored process
Dim prompts As SASPrompts
Set prompts = New SASPrompts

StpName = "/List_Stp_Test"

prompts.Add "Val_list", BG.Range("F12:F15").Value    'Not working


sas.Options.ShowSASLog = True

Set a1 = Sheets("Tool").Range("F17")

Sheets("Tool").Range("A1").Select

sas.InsertStoredProcess StpName, a1, prompts

 

 

Highlighted
Super Contributor
Posts: 284

Re: Running Stored Process using VBA - Facing issues in passing prompt values

[ Edited ]

any errors/notes/warnings from the sas log? did you check this sample note out - http://support.sas.com/kb/42/983.html

there is a sugi paper too http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

Ask a Question
Discussion stats
  • 1 reply
  • 189 views
  • 0 likes
  • 2 in conversation