BookmarkSubscribeRSS Feed
Fluorite | Level 6

Hi All, 

I know this may seem like a lot and its causing me a headache. 

So, I have two XLSX files that contain important data for my report.. one XLSX N(x1) has pivot tables and the other XLSX (X) has the formatted dashboard report that I need. 


My dilemma is when I go to merge them to one file. The code below will work in merging them together, however it wont preserve (meaning keeping active) the pivot table.


I am wondering if there is a way that I have not tried- that will preserve my pivot tables? The way I created the XLSX (X1) was through using a template that had the pivot tables.. but I had to export separately because my dashboard would just overwrite the template when exporting. 


I hope that makes sense-- this is how I am merging the two files: 

/* Merge base report and pivot template together to create final file */
libname x1 xlsx "/sasprod/client/reports/operational/membership_discrepency/member_discrepency_PIVOT_&rundate..xlsx";
libname x xlsx "/sasprod/client/reports/operational/membership_discrepency/member_discrepency_&rundate..xlsx";

data x.Cap_Affil_Termed;
set x1.Cap_Affil_Termed;

data x.Cap_Non_Cap_Contract;
set x1.Cap_Non_Cap_Contract;

data x.'Cap Affil Termed- BY TIN'n;
set x1.'Cap Affil Termed- BY TIN'n;

data x.'Cap Non Cap Contract- BY TIN'n;
set x1.'Cap Non Cap Contract- BY TIN'n;


Opal | Level 21

The simple answer is to ensure your pivot tables are stored in a different Excel tab to the tabs you are maintaining with SAS. That way you will minimise the danger of overwriting them. It's easy enough to have pivot tables built off data in another tab.

Fluorite | Level 6

Thankyou for your response!

Can you explain more what you mean about maintaining the pivots in another tab? 

I did use a template to export the raw data to the workbook with the pivot tables that way it updates the pivots automatically. However, the issue is that I cant export the ODS proc report into the template without the ODS proc report overwriting the template (and losing the pivots) all together. 

Opal | Level 21

If you are using ODS, then you have no choice but to recreate your Excel workbook each time, including pivot tables. Another way to do this would be to export your SAS data to one worksheet in your already existing Excel workbook, maybe using PROC EXPORT, but then manually create your pivot table based on that data in another worksheet. That way, whenever you update the Excel base data worksheet, your pivot table will update as well.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2 in conversation