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

[Kelkro]

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

[PaigeMiller]

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.

[Tom]

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?

[Kelkro]

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. 

[Kelkro]

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

[PaigeMiller]

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.