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

Change data source of SAS pivot tables in Excel

Accepted Solution Solved
Reply
Highlighted
Frequent Contributor
Posts: 82
Accepted Solution

Change data source of SAS pivot tables in Excel

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.

 


Accepted Solutions
Solution
‎10-23-2017 07:19 PM
SAS Employee
Posts: 24

Re: Change data source of SAS pivot tables in Excel

@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.

View solution in original post


All Replies
Super User
Posts: 23,683

Re: Change data source of SAS pivot tables in Excel

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

Super User
Super User
Posts: 9,599

Re: Change data source of SAS pivot tables in Excel

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. 

Super User
Posts: 10,211

Re: Change data source of SAS pivot tables in Excel

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 391

Re: Change data source of SAS pivot tables in Excel

Which version are you using?

I am able to change data source:

changeDataSource.PNG

"Ändra datakälla" means "change data source" Smiley Happy

 

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

//Fredrik

Solution
‎10-23-2017 07:19 PM
SAS Employee
Posts: 24

Re: Change data source of SAS pivot tables in Excel

@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.

Frequent Contributor
Posts: 82

Re: Change data source of SAS pivot tables in Excel

Posted in reply to fifthand57th
Great answer! Thanks.
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 1222 views
  • 5 likes
  • 6 in conversation