topic Re: Load output from stored process directly into a pivot table? in Microsoft Integration with SAS

Load output from stored process directly into a pivot table?

ScottBass — Wed, 21 Nov 2012 06:34:01 GMT

Hi,

SAS 9.3

SAS Add-in for Microsoft Office (AMO) 5.1 (5.100.0.12019) (32-bit)

Microsoft Office 2007

Using SAS AMO, do this:

SAS Tab--> SAS Data --> Browse to desired dataset (eg. SASHELP.STOCKS) --> Select Pivot Table, New Worksheet

So far, so good. I can load a SAS dataset directly into a Pivot Table.

Now, using a stored process (an AMO Report), is there any way to load the output of the stored process directly into a Pivot Table? I know I can load the data into an Excel worksheet, then use that worksheet as a source for the Pivot Table, but that's not what I want.

I was hoping I could do this using some setting in the stored process, such as a Data Target (output stream) (which I've never used in my stored processes).

Thanks,

Scott

Re: Load output from stored process directly into a pivot table?

FredrikE — Wed, 21 Nov 2012 12:43:38 GMT

One way could be to use a information map instead, see note:

http://support.sas.com/kb/39/432.html

I have succesfully tested to open a information map based on a start schema directly into excel pivot!

Regards

Fredrik

Re: Load output from stored process directly into a pivot table?

ScottBass — Wed, 21 Nov 2012 21:01:19 GMT

Thanks Fredrik. We only have a BI not EBI installation, so no Information Map Studio.

From my usage of AMO, I don't think what we want to do is possible; perhaps someone from SI can confirm this?

As I see it, we could dump the output from the stored process into a hidden worksheet, then build a pivot table from that using Excel's native functionality. But that's not ideal.

Perhaps we can do more using these approaches, but I'm not sure we want to go there: http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

Regards,

Scott

Re: Load output from stored process directly into a pivot table?

Reeza — Wed, 21 Nov 2012 22:04:22 GMT

Tableeditor tagsets?

http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html

Re: Load output from stored process directly into a pivot table?

Cynthia_sas — Thu, 22 Nov 2012 03:34:46 GMT

Hi:

The SAS Add-in for Microsoft Office can only accept HTML, CSV and SASReport results from a stored process. Even though TAGSETS.TABLEEDITOR is HTML, it also contains embedded JavaScript, which may not execute from inside the Add-in when the stored process returns results.

The other thing about TAGSETS.TABLEEDITOR is that you generally open the output in a browser and then click a button to save to Excel (from the browser). When you use a stored process, within the context of the SAS Add-in, the Office Add-in is NOT a browser -- it is Word or PowerPoint or Outlook or Excel. It may be possible to run TAGSETS.TABLEEDITOR using the Information Delivery Portal or the Stored Process Web App. This would be a question to ask of Tech Support.

cynthia

Re: Load output from stored process directly into a pivot table?

FredrikE — Thu, 22 Nov 2012 06:32:32 GMT

Ok, I don't know why you want to run a stored process, if it's because you want the most recent data och the ability to subset data before you open the report.

One way to at least get the latest data is to add your code before the task code. If you open properites from the SAS add-in tab in excel and chose tasks, you can add code before and after the task. I think that if you add code that creates a table which you have registred in metadata you can first update/create that table and then open it in Excel.

So:

1. create table

2. Register in metadata.

3. create code that updates/recreate table

4. add code before task in Excel.

5. open table in pivot.

Migtht work?!

Regards Fredrik