How would I utilize the libname in SAS to preserve pivot table in XLSX?

[Kelkro]

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;
run;

data x.Cap_Non_Cap_Contract;
set x1.Cap_Non_Cap_Contract;
run;

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

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

[SASKiwi]

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.

[Kelkro]

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. 

[SASKiwi]

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.