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

Madhup — Wed, 21 Feb 2018 20:43:01 GMT

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

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

saspert — Wed, 07 Mar 2018 19:58:25 GMT

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

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

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

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