The SAS Output Delivery System and reporting techniques

Mainframe Excel Output

Reply
N/A
Posts: 0

Mainframe Excel Output

I have coded a sas program on a mainframe to use ODS to generate a PDF file containing some results and then email the results to me.

I am now trying to create an excel spreadsheet instead of PDF. I have tried to use ODS HTML in my mainframe sas job and then change the extension to xls when emailing.

When I send the file I add the extension and this does not seem to work, as a the output file doesn’t seem to be in excel or HTML format. Is there a way to create excel ODS output from the mainframe? I think the mainframe I am using is running sas version 8.
N/A
Posts: 0

Re: Mainframe Excel Output

I was using a CT option in my email attach statement. This was causing the format to come out in a strange format.

Works OK now.
Contributor
Posts: 28

Re: Mainframe Excel Output

Would you please give me your sample code? Can I create multiple sheets excel file from mainframe?
SAS Super FREQ
Posts: 8,743

Re: Mainframe Excel Output

Hi:
When you use ODS to create files, you are NOT creating true, binary Excel files. You are creating Markup Language files -- either HTML markup, comma separated markup or XML markup -- ASCII text files -- that Excel knows how to open and render. Refer to this previous posting for more discussion:
http://support.sas.com/forums/thread.jspa?messageID=8719∏

When you create an ODS HTML file, for example, you would normally give that file a file extension of .HTML. However, for convenience purposes, many people do this:
[pre]
ods html file='c:\temp\myfile.xls'; OR
ods csv file='c:\temp\myfile.csv'; OR
ods msoffice2k file='c:\temp\toXLS_mso.xls';
[/pre]


and give any of these files an .XLS extension it is merely for the convenience of "fooling" the Windows registry into opening Excel as the result of a double-click action (instead of launching the browser (as for an HTML or an XML file).

As for your question about creating multiple sheet workbooks, the only destination that easily creates multi-sheet workbooks is TAGSETS.EXCELXP. If you run the code shown below, you will see that only TAGSETS.EXCELXP output (in SAS 9) will create multiple sheets, one for each PROC REPORT step. For all the other destination output (HTML and CSV), the file that Excel displays will have both procedure outputs in one worksheet.

cynthia

[pre]
ods listing close;
ods csv file='comma_sep.csv';
ods html4 file='toXLS_HT4.xls' style=sasweb;
ods html3 file='toXLS_HT3.xls' style=sasweb;
ods msoffice2K file='toXLS_MSO.xls' style=sasweb;
ods tagsets.excelxp file='toXLS_XP.xls' style=sasweb
options(doc='Help');

proc report data=sashelp.class nowd;
title 'First Proc Report';
column Name Age Height Weight;
define Name /order;
define Age / display;
define height / display;
define weight / display;
run;

proc report data=sashelp.class nowd;
title 'Second Proc Report';
column Age Sex n height weight;
define Age / group;
define sex / group;
define n / 'Count';
define height / mean 'Avg Height';
define weight / mean 'Avg Weight';
rbreak after / summarize;
run;
ods _all_ close;

ods listing;
title; footnote;

[/pre]
Contributor
Posts: 28

Re: Mainframe Excel Output

We're still using SAS 8.2 in Mainframe. It doesn't work when I ftp the binary file to PC.
Super Contributor
Super Contributor
Posts: 3,174

Re: Mainframe Excel Output

Honestly, you desperately need to get up to a supported SAS version. And now consider that you are two major versions behind. So much to gain from the many enhancements and you're enterprise/organization is paying for the features through maintenance/renewal yet you are not able to use them. How unfortunate.

Scott Barry
SBBWorks, Inc.

Migration
Lead Your Organization to SAS 9
http://support.sas.com/rnd/migration/index.html
http://support.sas.com/rnd/migration/papers/index.html - papers on the topic
http://support.sas.com/rnd/migration/papers/peaceful.html - migration path with parallel-cast testing opportunities

Usage Note 12814: SAS 9 Migration Considerations and Compatibility Changes for z/OS or OS/390
http://support.sas.com/kb/12/814.html
Maintenance Release Announcement for SAS 9.2
http://support.sas.com/software/maintenance/index.html

FOCUS AREA - Base SAS
http://support.sas.com/rnd/base/index.html
SAS Super FREQ
Posts: 8,743

Re: Mainframe Excel Output

I am so confused...what binary file?? ODS HTML, ODS CSV, ODS TAGSETS.EXCELXP output files are all TEXT files. They probably would not FTP correctly as binary -- since they are TEXT files.

ODS RTF is also a TEXT file. ODS PDF, ODS PS, ODS PRINTER and ODS PCL are proprietary formats -- I would suspect that they will need to be FTP'd as binary files.

Your best bet at this point, is to work with Tech Support.

cynthia
Contributor
Posts: 28

Re: Mainframe Excel Output

When I use ods pdf in mainframe, it's a binary file. I need to ftp the binary file from mainframe to pc after the sas program.
Super Contributor
Super Contributor
Posts: 3,174

Re: Mainframe Excel Output

FYI - I believe that ODS PDF was experimental with SAS version 8.x (possibly only with 8.1 though) and as I recall it didn't work well on the IBM mainframe (OS/390, z/OS MVS) platform.

Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,175

Re: Mainframe Excel Output

with some success at a mainframe site (about a year ago) we created tagsets.excelxp output that was fit to email (or and/or deliver with connect:direct to lan servers). The major but simple stumbling block was resolved with 2 "none"s
url=none enabled the links on index and TOC
rs=none (it means no row separators) allowed the delivery of plain text with a separate line for each cell.
In keeping with the microsoft future of "extension hardening" we delivered to a file with the .xml extension rather than .xls

it all worked (once we had dealt with the hard part == getting the updated proc template code for tagsets.excelxp, to run on our ibm mainframe with non-north-america SAS locale Smiley Happy
The experience left deeply entrenched memories, ;-) and a few postings in this forum.


PeterC
Super Contributor
Super Contributor
Posts: 3,174

Re: Mainframe Excel Output

Hopefully you shared your experience with SAS Tech Support, with the idea that they might want to embrace whatever challenges you had and improve the feature that much better?

Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,175

Re: Mainframe Excel Output

Scott,
SAS Customer Support in UK have tracked the problem.
I chronicled the ensuing resolution in this forum starting with the problem in October 2007 http://support.sas.com/forums/thread.jspa?messageID=8004 . By 4APR2008 (after SGF) I was able to report successful upgrade. The thread on this forum attracted interest from others who were suffering the same difficulties.

PeterC
New Contributor
Posts: 4

Re: Mainframe Excel Output

Hi,

 

If you dont mind please can you share the JCL and SAS ODS code you used, to export or generate to PDF.

I am trying to use ODS and generate a PDF and excel file (tagsets.excelxp), but htere is no luck. If you can share that will be of great help.

 

Thank you

Ask a Question
Discussion stats
  • 12 replies
  • 1986 views
  • 0 likes
  • 6 in conversation