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

Can't Replace Excel Pivot Tables with SAS Dataset Pivot Tables

Reply
New Contributor
Posts: 2

Can't Replace Excel Pivot Tables with SAS Dataset Pivot Tables

Hello Guys.

 

I built reports and pivot table with slicers in Microsoft Excel, using a Excel table in a different sheet.

Last couple days, the IT team installed SAS Add-in for Office in my computer and I'm trying to replace the pivot tables from Excel with Pivot Tables from SAS datasets.

 

The problem is: I don't know how to do it.

I clicked on a random pivot table, then I selected Pivot Table Tools --» Options --» Change Data Source --» and after that, I have 2 options. The first one is: Select a table or range (already selected with my Excel table) and the second one is 'use and external data source' but is not ativated or it is disabled.

 

So, how do I replace my Excel pivot tables with SAS Dataset pivot tables?

PROC Star
Posts: 7,357

Re: Can't Replace Excel Pivot Tables with SAS Dataset Pivot Tables

What do you mean by a 'SAS Dataset Pivot Table'? Do you just want to replace the Excel table that contains a dataset that is driving the pivot table with a SAS dataset .. but keeping all of the existing linkages to the existing pivot table?

 

Are the column headers (variable names) the same in both the Excel file and the SAS dataset and you just want to update the data table?

 

Art, CEO, AnalystFinder.com

 

New Contributor
Posts: 2

Re: Can't Replace Excel Pivot Tables with SAS Dataset Pivot Tables

Hello Art,

 

Thanks for the answer. 

 

Well, when I use te SAS tab and I open data, I can choose to reply the sas dataset in a worksheet or to open it as Pivot Table. 

 

So what I want to do is to replace Pivot Tables (from Excel worksheet) with SAS datasets (as pivot tables). Mainly because I've done like more than 20 dashboards using those pivot tables, and now I want to use SAS pivot tables instead of my Excel pivot tables. Do you understand?

 

 

I could replace the Excel worksheet (my source for the pivot table) with a table from SAS, but that would not make my point, which is tryin' to save some memory and space from the Excel file (the file is up to 300 MB).

 

Yes the column headers are the same.

 

 

Thank you in advance,

Tiago 

Respected Advisor
Posts: 3,887

Re: Can't Replace Excel Pivot Tables with SAS Dataset Pivot Tables

The pivot table as such is in Excel. What you want to do is to change the data source for this pivot table from an Excel range to an external data source.

 

It appears that's something you can't do in Excel and if you want to avoid a big change then I guess it's going for your option "replace the Excel worksheet (my source for the pivot table) with a table from SAS"

 

 

"To base your PivotTable on a different external source, it might be best to create a new PivotTable."

https://support.office.com/en-us/article/Change-the-source-data-for-a-PivotTable-ad8ed968-ada1-4dde-...

 

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 197 views
  • 0 likes
  • 3 in conversation