Changing SAS data source in excel 2010 pivot

ccondike — Tue, 25 Mar 2014 19:31:50 GMT

I have a set of pivots connected to a SAS data source and I'm looking to use the same set of pivots I built, but connect to a different SAS data source.

I spent ~20 hours building an excel report built of approximately 10 individual pivots, slicers, formulas, etc. I need this report replicated with the exact same fields but instead, connect to 7 different data sets in SAS. I'm hoping I don't have to spend another ~100 hours rebuilding all of this from scratch.

When I click on the current pivot table I have connected to "SAS data set X", I go to the SAS tab in excel --> SAS Data, I can browse for the new SAS data set, "data set Y" and locate it. Once I have "data set Y" selected, I'm unable to get it to replace the data source of the pivot table I clicked on.

The only option I have is to connect "data set Y" to a blank pivot, which would mean I would have to run through that entire 20 hour process again.

Does anyone know how to simply change the SAS data source in an existing pivot?

Thanks

Chris

Re: Changing SAS data source in excel 2010 pivot

DomPazz — Thu, 27 Mar 2014 16:01:07 GMT

I think you have to use VBA to change the source. Open the SAS Add-In for Microsoft Office help (SAS Ribbon -> Help -> Help for the SAS Add-In to Microsoft Office). From there, look at the chapter named "Automating the SAS Add-In with Visual Basic Code". There is documentation on the SASPivotTable class.

I have not tried this, but this is where I would start if I were in your shoes.

HTH!

topic Re: Changing SAS data source in excel 2010 pivot in Microsoft Integration with SAS

Changing SAS data source in excel 2010 pivot

Re: Changing SAS data source in excel 2010 pivot