SAS AMO - Passing Output Parameters To Excel

[Scott_Mitchell]

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

[fifthand57th]

Hi @Scott_Mitchell,

Do the stored process and output parameters behave as expected without VBA involved? If you just run the stored process interactively, do you receive different results? This will tell us if the issue is in the VBA or the SAS stored process.

Check out this paper from page 14 to the end: http://support.sas.com/resources/papers/proceedings12/036-2012.pdf.

It steps through setting up a similar stored process with output parameters, but doesn't add VBA until the stored process works interactively first.

[Scott_Mitchell]

Thank you for the paper @fifthand57th.

I have employed the method of checking the results discussed in the paper and I am getting the anticipated results:

So the issue is the VBA (which doesn't surprise me as I haven't used VBA in about 10 years).

I will continue working on this issue, but if you have an suggestions I would greatly appreciate any insights.

Regards,

Scott

[Scott_Mitchell]

@fifthand57th - It turned out to be a very simple (in hindsight at least) VBA issue, stemming from the original code attached to the article referenced previously.

    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

My understanding of this chunk of code initially was, if the STP already existed then it would perform a refresh of the input parameters and output parameters that have been defined and re-execute the STP using the newly defined parameters then it would exit the sub routine.  In actuality (from what I am now observing), the stored process is definitely passing the input parameter name to the STP, but it had no idea what output parameters were to be returned (I assume this is because the first time I executed the STP I didn't hadn't defined output parameters yet within the VBA).  I deleted the STP from the Excel spreadsheet using the AMO interface and executed it again and bingo everything populates as desired.  I then removed the offending code and replaced it with code which deletes the STP from the Excel Sheet before inserting the STP again.  It now works like a charm.

    
Sub DeleteAllStoredProcesses()

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

    Dim list As SASStoredProcesses
    Set list = sas.GetStoredProcesses(Sheet1)
    
    If list.Count() > 0 Then
    
        Dim stp As SASStoredProcess
        Set stp = list.Item(1)
        stp.Delete
    
    End If

End Sub


Sub DeleteandPullFromDS()

    DeleteAllStoredProcesses
    PULL_FROM_SAS_DATASET

End Sub

For completeness sake, is there anything I am saying above that doesn't stand true?

Thank you again for all your help.

Regards.

Scott