topic Re: How to create an active pivot table from SAS to XLSX sheet in SAS Studio

How to create an active pivot table from SAS to XLSX sheet

Kelkro — Wed, 20 Sep 2023 17:39:01 GMT

Hi all,

I have been trying many things to get SAS to recognize a pivot table and have no luck in keeping the PIVOT Table active when finally exporting to excel.

that leads me to my question; can you help me understand how to create a pivot table from SAS to XLSX.. I do not want it to be an HTML file where I have to click the button on the results.

Thanks!!!

Re: How to create an active pivot table from SAS to XLSX sheet

PaigeMiller — Wed, 20 Sep 2023 18:52:24 GMT

Let me ask for clarification.

What does it mean "to create a pivot table from SAS to XLSX"? I don't even understand the sentence. There are no true pivot tables in SAS, but there could certainly be SAS results that have the same numerical values as a pivot table in Excel. Please provide a lot more detail here.

Re: How to create an active pivot table from SAS to XLSX sheet

Tom — Wed, 20 Sep 2023 17:58:53 GMT

My limited understanding of "pivot" tables in Excel is they are tools for generating summaries.

If you want summaries use PROC SUMMARY and just write the result into the report.

Why do you think you need a pivot table?

Re: How to create an active pivot table from SAS to XLSX sheet

Kelkro — Wed, 20 Sep 2023 19:25:43 GMT

Proc tabulate won't work as I need an active pivot table in the export. This is a client facing report and the client wants it that way.

Re: How to create an active pivot table from SAS to XLSX sheet

Kelkro — Wed, 20 Sep 2023 19:26:42 GMT

This is the code I have been working with but it wont product the pivot table as expected.

ods excel file="/sasprod/client/reports/operational/membership_discrepency/temp.xlsx" options(sheet_name="shoe_report");

proc print data=sashelp.shoes;

run;

ods excel close;

ods tagsets.tableeditor file="/sasprod/client/programs/sasprod/programs/financials/outboundcap/HY/OUTDATA/tableeditor.tpl"

options(

update_target="\\sasprod\\client\\reports\\operational\\membership_discrepency\\temp.xlsx"

sheet_name="shoe_report"

pivot_sheet_name="Profit Analysis"

pivotrow="region"

pivotcol="product"

pivotdata="sales"

);

run;

ods tagsets.tableeditor close;

x "/sasprod/client/programs/sasprod/programs/financials/outboundcap/HY/OUTDATA/tableeditor.tpl" ;

Re: How to create an active pivot table from SAS to XLSX sheet

PaigeMiller — Wed, 20 Sep 2023 19:32:42 GMT

So now that you mention that you are having a specific problem with tagsets.tableeditor, a solution is found easily by googling tagsets.tableeditor.

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-TAGSETS-TABLEEDITOR/td-p/100168

Google finds other solutions as well.