Your SAS programs, embedded in web apps and elsewhere

Passing data from Excel to a Stored Process via AMO

Reply
Super Contributor
Posts: 387

Passing data from Excel to a Stored Process via AMO

Hi,

Summary:  Do you have an example, or can you point me to one, of passing data from Excel to a Stored Process via AMO and VBA?  Note that there are a lot of good tips here:  http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

Details:  These steps represent my issue:

Do this in Excel:

Sheet 1:  $A$1 = "Stock", $A$2 = "Start Date", $A$3 = "End Date".  Format $B$1 and $B$2 as Date, dd-mon-yy.  Created named ranges $B$1 = STOCK, $B$2 = STARTDATE, $B$3 = ENDDATE.  This is needed to run the VBA code below without needing to modify it.

Sheet 2:  Use the AMO SAS Data icon to drop SASHELP.STOCKS into Sheet 2.  Tick "Display data source and filter information in the worksheet".

Sheet 3:  I'd like to filter SASHELP.STOCKS based on the values in $B$1 : $B$3 and generate some basic statistics.  To do this, I assume I need a stored process - none of the summary stats tasks appear to support a where clause.

So say my stored process is something like:

proc sql;

  select stock, min(close) as min_close, max(close) as max_close from sashelp.stocks where stock = "&stock" and date ge "&startdate"d and date le "&enddate"d group by stock;

quit;

I need the where clause used by the stored process to be dynamically generated by the data in Sheet 1.

I've got VBA code which derives a filter string and passes that to the SAS Data View object.  Can I somehow pass this (a VBA variable containing valid where syntax) to the stored process?

Here is my VBA code.  Let me know if it can be improved - I'm certainly no VBA expert.

Thanks,

Scott

================================================================

Sub FilterAllData()

    ' Declare variables

    ' SAS AMO Objects

    Dim oSAS As SASExcelAddIn

    Dim oData As SASDataView

    Dim oListData As SASDataViews

    Dim sName As String

   

    ' Filter Variables

    Dim sFilter As String

    Dim sStock As String

    Dim dStartDate, dEndDate As Date

    Dim sStartDate, sEndDate As String

   

    ' Set Filter string

    sFilter = Missing

    sStock = Worksheets("Sheet1").Range("STOCK")

    dStartDate = Worksheets("Sheet1").Range("STARTDATE")

    dEndDate = Worksheets("Sheet1").Range("ENDDATE")

   

    If sStock <> Missing Then

        sStock = "stock = '" + sStock + "'"

    End If

    If dStartDate <> Missing Then

        sStartDate = "date >= '" + UCase(Format(dStartDate, "ddmmmyyyy")) + "'d"

    End If

    If dEndDate <> Missing Then

        sEndDate = "date <= '" + UCase(Format(dEndDate, "ddmmmyyyy")) + "'d"

    End If

   

    If sStock <> Missing Then

        If sFilter <> Missing Then

            sFilter = sFilter + " AND "

        End If

        sFilter = sFilter + sStock

    End If

   

    If sStartDate <> Missing Then

        If sFilter <> Missing Then

            sFilter = sFilter + " AND "

        End If

        sFilter = sFilter + sStartDate

    End If

   

    If sEndDate <> Missing Then

        If sFilter <> Missing Then

            sFilter = sFilter + " AND "

        End If

        sFilter = sFilter + sEndDate

    End If

   

    ' Now apply the filter to all SAS Data Views in this workbook

    Set oSAS = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

   

    ' All data views in this workbook

    Set oDataViews = oSAS.GetDataViews(ThisWorkbook)

   

    For i = 1 To oDataViews.Count

        Set oData = oDataViews.Item(i)

        sName = UCase(oData.DisplayName)

        Select Case sName

            Case "SASAPPSmiley FrustratedASHELP.CLASS"

                ' do nothing

            Case Else

                oData.Filter = sFilter

                oData.Sort = "stock"

        End Select

        oData.DisplayAllRecords = True

        oData.Refresh

    Next i

   

    MsgBox "Refresh Complete"

End Sub

Ask a Question
Discussion stats
  • 0 replies
  • 577 views
  • 0 likes
  • 1 in conversation