ODS and Base Reporting

Build reports by using ODS to create HTML, PDF, RTF, Excel, text reports and more!
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
P_S_
Obsidian | Level 7

Hello,

I am using ods tableeditor tagsets to create an excel pivot table from sas.

I am using sashelp.shoes dataset for my example.

I have region and product as my pivotrow variables. For my pivotdata variables I am using stores and inventory and I am using pivotdata_stats = "sum".

Now the problem I am having is the sum for stores and inventory appear in the rows in the excel spreadsheet. I would like these stats to appear in the column.

Is there a way to do this directly from sas?


Thank you,

Here is the code:

ods tagsets.tableeditor file="C:\exampleaa.html"

options(

button_text = "Create Pivot"

auto_excel = "yes"

pivotrow = "region,product"

pivotdata="stores,inventory"

pivotdata_stats="sum"

pivot_format="light10"

sheet_name="TEST"

quit="NO"

);

proc print data=sashelp.shoes;

run;

ods tagsets.tableeditor close;


undefined
1 ACCEPTED SOLUTION

Accepted Solutions
Chevell_sas
SAS Employee

To have this appear as columns rather than rows, add the PIVOTDATA_TOCOLUMNS="yes" option which should do this.

View solution in original post

8 REPLIES 8
shalinisas
Calcite | Level 5

SAS support Can you please help I have the same problem!!!!!

Chevell_sas
SAS Employee

To have this appear as columns rather than rows, add the PIVOTDATA_TOCOLUMNS="yes" option which should do this.

P_S_
Obsidian | Level 7

Works !!!. Thanks Chevell@sas.

neha_sas
Calcite | Level 5

Hi Chevell,

I saw your post on tagset table editor. I was wondering if I could directly create Pivot table from sas instead of creating a HTML file with a " Create Pivottable " button on it.

The reason i ask is that I need to export the SAS data into a Pivot in Unix and upload it at a certain location.

Also, the data I am dealing with is huge and opening a HTML file is taking a lot of time , let alone the time it takes to create the Pivot Table

Second question: Is there any option that can be used to just retain the excel tab with Pivot table and delete the rest of the sheets ( including data sheet).

Thanks

Reeza
Super User

Post your question in a new thread.

neha_sas
Calcite | Level 5

I am not able to open a new thread !

neha_sas
Calcite | Level 5

Thanks Reeza, I have now posted it as a new question.

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 6793 views
  • 1 like
  • 5 in conversation