BookmarkSubscribeRSS Feed
Kelkro
Fluorite | Level 6

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

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Diamond | Level 26

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.

--
Paige Miller

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1165 views
  • 0 likes
  • 3 in conversation