03-25-2014 03:31 PM
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?
03-27-2014 12:01 PM
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.