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;


6-18-2013 9-42-50 AM.png
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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5121 views
  • 1 like
  • 5 in conversation