Mainframe SAS - ODS - Pivot tables/Chart

Reply
Contributor
Posts: 23

Mainframe SAS - ODS - Pivot tables/Chart

Hi

I was wondering if there is a way to create Pivot tables/chart using Mainframe SAS ODS? If so, can someone point to a learning guide or a tutorial?

I searched and keep seeing Pivot is not supported as one of the output formats in Mainframe SAS 9.2 version. Does 9.3 support it? Thanks for your time and help.

SAS Super FREQ
Posts: 8,861

Re: Mainframe SAS - ODS - Pivot tables/Chart

Posted in reply to prasanna_sk

Hi:

  What are you looking at where you see that Pivot tables are not supported? Do you mean using the Enterprise BI server and the SAS Add-in for Microsoft Office or do you mean that you are using TAGSETS.TABLEEDITOR on the mainframe?

  The way that TAGSETS.TABLEEDITOR works is by embedding JavaScript inside an HTML output file that writes to Excel when the HTML file is opened. Whether or not TAGSETS.TABLEEDITOR (or the BI Platform method of making Pivot tables) works in 9.2 or 9.3 is really a question for Tech Support.

cynthia

Contributor
Posts: 23

Re: Mainframe SAS - ODS - Pivot tables/Chart

Posted in reply to Cynthia_sas

Hi Cynthia

I wanted to know if it is possible to create Pivot Table from Mainframe SAS v9.3. If so, how?

I have a pivot tablein  .xls file that has 2 pivot tables and 2 pivot charts that were created using Microsoft Excel manually. It needs to be automated. I was wondering if it is possible to do that entirely in mainframe SAS and then put the excel file over to some specified location.

SAS Super FREQ
Posts: 8,861

Re: Mainframe SAS - ODS - Pivot tables/Chart

Posted in reply to prasanna_sk

Hi:

  As far as I know, the only way to make a true pivot table in an Excel format is to use Excel techniques. When SAS exports data to Excel, using PROC EXPORT or the Libname Engine, I do not believe that SAS export techniques can write to an Excel pivot structure. When you use ODS, you are not creating true, binary Excel files. You are only creating ASCII text files that Excel knows how to open and render as sheet(s) in a workbook.  The tableeditor tagset uses HTML, CSS, and JavaScript to make HTML files -- not Excel files.

  I do not know whether there is an HTML or XML Spreadsheet specification for a pivot table. I believe that TAGSETS.TABLEEDITOR needs to use JavaScript because there is NOT a Microsoft direct HTML or XML spec for a pivot table. But again, this is a question for SAS Tech Support. Since ODS doesn't make true, binary Excel files, you really should work with Tech Support.

cynthia

Contributor
Posts: 23

Re: Mainframe SAS - ODS - Pivot tables/Chart

Posted in reply to Cynthia_sas

Thanks Cynthia. This helped.

PROC Star
Posts: 1,167

Re: Mainframe SAS - ODS - Pivot tables/Chart

Posted in reply to prasanna_sk

Perhaps another option is to write the source data for your pivot table to Excel, and (maybe) you could use Excel automation to create the pivot table within Excel?

Tom

Contributor
Posts: 23

Re: Mainframe SAS - ODS - Pivot tables/Chart

Thanks for the response Tom. Yes, we are going to try that method.

Ask a Question
Discussion stats
  • 6 replies
  • 503 views
  • 3 likes
  • 3 in conversation