BookmarkSubscribeRSS Feed
tiagolis
Calcite | Level 5

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?

3 REPLIES 3
art297
Opal | Level 21

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

 

tiagolis
Calcite | Level 5

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 

Patrick
Opal | Level 21

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

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 3 replies
  • 1257 views
  • 0 likes
  • 3 in conversation