Good Afternoon Everyone,
I am hoping someone may be able to help me as I am pulling my hair out on this one.
I am required to pull back a single record to Excel based on a series of Input Parameters. For the sake of brevity I have limited the example to a single Input Parameter and a Single Output Parameter within the VBA code. The reason I am using Output Parameters as opposed to reading in a single record back into Excel is because the results need to be output to various cells within the spreadsheet and not in tabular form.
Example of the Desired Output
VBA
Sub PULL_FROM_SAS_DATASET()
Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
'Delete and entries in the existing log
sas.ClearLog
'Setting Options
sas.Options.ResetAll
sas.Options.AutoInsertResultsIntoDocument = True
sas.Options.PromptForParametersOnRefreshMultiple = False
sas.Options.ShowStatusWindow = False
sas.Options.ShowStatusWindow = False
sas.Options.ShowSASLog = True
sas.Options.ShowStatusWindow = False
sas.Options.Excel.ShowDataInfoInStatusBar = False
sas.Options.Excel.ShowPlaceholderForEmptyResults = False
'Create a list of all stored processes within the workbook.
Dim list As SASStoredProcesses
Set list = sas.GetStoredProcesses(ThisWorkbook)
'If the stored process within the is equal to the the value stored in stp.path then refresh the results
For i = 1 To list.Count
Dim stp As SASStoredProcess
Set stp = list.Item(i)
' Change the path below to the folder where your stored process was saved
If (stp.Path = "/User Folders/scm/My Folder/Motor Premium Emulator Proof of Concept Pull Data V0_1") Then
stp.Refresh
Exit Sub
End If
Next i
' Input streams for the stored process
Dim prompts As SASPrompts
Set prompts = sas.CreateSASPromptsObject
' Modify the range where the user is typing in the input data.
' Make sure this range includes the label that will be used as the variable name
Dim IN_NAME As Range
Set IN_NAME = Sheet1.Range("IN_NAME")
prompts.Add "IN_NAME", IN_NAME
' Output parameter locations that have been added to the stored process
Dim outputParams As SASRanges
Set outputParams = New SASRanges
Dim OUT_NAME As Range
Set OUT_NAME = Sheet1.Range("OUT_NAME")
outputParams.Add "OUT_NAME", Sheet1.Range("B5")
'Capture The Prompts To Be Used As Parameters Within The Stored Process
' Modify the parameters for the method below to the location of your stored process
' Here is the syntax of the method
' object.InsertStoredProcess(path,outputLocation,[prompts],[outputParameters],[inputStreams])
Set stp = sas.InsertStoredProcess("/User Folders/scm/My Folder/Motor Premium Emulator Proof of Concept Pull Data V0_1", Sheet1.Range("A20"), prompts, outputParams)
End Sub
I was inspired by http://support.sas.com/kb/42/983.html in my approach to the proposed solution, however instead of an input stream, I am using Input Parameters (because the information used for Input Parameters will be in various places within the sheet and will be non-contiguous).
Stored Process
%IF %LENGTH(&IN_NAME.) > 0 %THEN %DO;
DATA PULL_PRICING_LOGS;
SET SASHELP.CLASS;
WHERE UPCASE(NAME) = UPCASE("&IN_NAME.");
CALL SYMPUTX("OUT_NAME",NAME,"GLOBAL");
CALL SYMPUTX("OUT_AGE",AGE,"GLOBAL");
CALL SYMPUTX("OUT_SEX",SEX,"GLOBAL");
CALL SYMPUTX("OUT_WEIGHT",WEIGHT,"GLOBAL");
CALL SYMPUTX("OUT_HEIGHT",HEIGHT,"GLOBAL");
RUN;
%END;
%PUT &=OUT_NAME &=OUT_AGE &=OUT_SEX &=OUT_WEIGHT &=OUT_HEIGHT;
The VBA executes without errors and the relevant Input Parameters are passed to the Stored Process successfully. The following is output to the log and indicates that the macro variables are being created successfully.
Log Resulting From Stored Process Call
30 %IF %LENGTH(&IN_NAME.) > 0 %THEN %DO;
31
32 DATA PULL_PRICING_LOGS;
33 SET SASHELP.CLASS;
34 WHERE UPCASE(NAME) = UPCASE("&IN_NAME.");
35 CALL SYMPUTX("OUT_NAME",NAME,"GLOBAL");
36 CALL SYMPUTX("OUT_AGE",AGE,"GLOBAL");
37 CALL SYMPUTX("OUT_SEX",SEX,"GLOBAL");
38 CALL SYMPUTX("OUT_WEIGHT",WEIGHT,"GLOBAL");
39 CALL SYMPUTX("OUT_HEIGHT",HEIGHT,"GLOBAL");
40 RUN;
NOTE: There were 1 observations read from the data set SASHELP.CLASS.
WHERE UPCASE(NAME)='ALFRED';
NOTE: The data set WORK.PULL_PRICING_LOGS has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
41
42 %END;
43
44 %PUT &=OUT_NAME &=OUT_AGE &=OUT_SEX &=OUT_WEIGHT &=OUT_HEIGHT;
OUT_NAME=Alfred OUT_AGE=14 OUT_SEX=M OUT_WEIGHT=112.5 OUT_HEIGHT=69
Stored Process Created Parameters
Unfortunately, the results from the macro variables are not being passed back from SAS to Excel in order to populate the defined cell. If anyone has an idea as to where I am going wrong I would appreciate any insight you could offer.
@fifthand57th - Tagging you in this post, as you were so wonderfully helpful with my last query.
Thanking you all in advance.
Scott
... View more
