topic Re: Change data source of SAS pivot tables in Excel in Microsoft Integration with SAS

Change data source of SAS pivot tables in Excel

ayin — Mon, 23 Oct 2017 01:03:29 GMT

Started with importing SAS data into Excel spreadsheet in the format of Pivot Table. Then set up with all the fields in that pivot table (e.g. filters, rows, columns etc.).

The question is how to create more pivot tables with the same structure/fields but different data source?

We would normally repeat the steps above for different SAS data sources, but that's rather time consuming.
I've tried to copy the pivot table, and paste into a new worksheet. However, SAS add-in does not allow me to change the data source of the new pivot table. We don't know how to use Excel built-in function [ANALYZE -> Change Data Source] to change to another SAS data source either.

Re: Change data source of SAS pivot tables in Excel

Reeza — Mon, 23 Oct 2017 01:07:23 GMT

And this is why you never use GUI tools.....

Re: Change data source of SAS pivot tables in Excel

RW9 — Mon, 23 Oct 2017 07:57:28 GMT

You would be better off asking on an Excel forum about Excel matters, this is a SAS forum. If it was me, I would record a VBA macro, then modify that VBA macro to generate the required outcomes.

Re: Change data source of SAS pivot tables in Excel

Kurt_Bremser — Mon, 23 Oct 2017 08:29:06 GMT

And if it was me, I'd implement the whole logic in SAS and stay lightyears away from Excel.

SAS code can be versioned, logs can be kept for reference, the code can be made flexible by using macro features, and everything can easily be run by the organization's scheduling system.

Re: Change data source of SAS pivot tables in Excel

FredrikE — Mon, 23 Oct 2017 10:21:06 GMT

Which version are you using?

I am able to change data source:

"Ändra datakälla" means "change data source" 🙂

I am using excel version 14.0.6023.1000 and add-in 7.12 (7.100.2.3662)

//Fredrik

Re: Change data source of SAS pivot tables in Excel

fifthand57th — Mon, 23 Oct 2017 15:56:10 GMT

@ayin

On the SAS ribbon in Excel, click Tools -> Replicate SAS Content to copy the SAS-sourced PivotTable. Then highlight the replicated SAS Content and choose "Change Data Source".

Never copy and paste a PivotTable from one location to another in a workbook if you want to retain the SAS links to refresh the data. See http://support.sas.com/kb/44932.

Re: Change data source of SAS pivot tables in Excel

ayin — Mon, 23 Oct 2017 23:19:51 GMT

Great answer! Thanks.