BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
12 REPLIES 12
deleted_user
Not applicable
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.
Warren
Obsidian | Level 7
Would you please give me your sample code? Can I create multiple sheets excel file from mainframe?
Cynthia_sas
SAS Super FREQ
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]
Warren
Obsidian | Level 7
We're still using SAS 8.2 in Mainframe. It doesn't work when I ftp the binary file to PC.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Cynthia_sas
SAS Super FREQ
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
Warren
Obsidian | Level 7
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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 🙂
The experience left deeply entrenched memories, 😉 and a few postings in this forum.


PeterC
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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
rahul8krk
Calcite | Level 5

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

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
  • 12 replies
  • 4509 views
  • 0 likes
  • 6 in conversation