SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Dynamic queries using Addin for Microsoft Office

Accepted Solution Solved
Reply
Super Contributor
Posts: 386
Accepted Solution

Dynamic queries using Addin for Microsoft Office

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


Accepted Solutions
Solution
‎01-17-2013 06:26 AM
Super Contributor
Posts: 386

Re: Dynamic queries using Addin for Microsoft Office

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

View solution in original post


All Replies
Solution
‎01-17-2013 06:26 AM
Super Contributor
Posts: 386

Re: Dynamic queries using Addin for Microsoft Office

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 339 views
  • 0 likes
  • 1 in conversation