BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
fifthand57th
SAS Employee

@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

6 REPLIES 6
Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Kurt_Bremser
Super User

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.

 

FredrikE
Rhodochrosite | Level 12

Which version are you using?

I am able to change data source:

changeDataSource.PNG

"Ä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

fifthand57th
SAS Employee

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

ayin
Quartz | Level 8
Great answer! Thanks.

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
  • 6 replies
  • 7100 views
  • 7 likes
  • 6 in conversation