Dynamic queries using Addin for Microsoft Office

[ScottBass]

Hi,

Say I have an Excel workbook with 12 worksheets.  Each worksheet is loading data from 12 separate datasets.  However, all 12 datasets have a common structure, i.e. fromdate and todate. 

Each dataset is filtered by a desired date range each month.

Questions:

1) Loading the SAS data into Excel using SAS Data --> (View SAS Data Window) --> Browse data to select the data source --> Filter and sort... button:  Is there a way to dynamically set the values for the filter?  Ideally I'd type data in an Excel cell, and the filter would pick up that value from the cell. 

Since all 12 datasets are filtered by the same criteria, it would be nice to setup a dynamic filter for each query, then change the values in one spot for the 12 datasets.  We will be copying the Excel workbook, such as FOO_201301.xlsx, FOO_201302.xlsx, etc, each month, and it's kind of a pain to have to go into all 12 queries to update the hardcoded values for the fromdate and todate.

2) Same question as above, but using a stored process.  I thought I read somewhere that Excel data can be passed into a stored process, and thus control the data returned by the stored process?  Although not ideal - I'd prefer not to have to create a stored process - this could be a suitable workaround.

Thanks,

Scott

[ScottBass · Accepted solution]

Hi,

My questions above are answered in this SGF paper:  http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

I've also learned that there isn't any online documentation (i.e. http://support.sas.com documentation) for automating AMO.  The best resource is the help file in AMO.

Thanks,

Scott