Help using Base SAS procedures

Mainframe code to create simple bar chart in Excel, attach in e-mail

Reply
Frequent Contributor
Posts: 75

Mainframe code to create simple bar chart in Excel, attach in e-mail

I'm looking for ODS code to create a simpel verticle bar chart in Excel, and then attach in an e-mail, all from a mainframe.

I just finished upgrading to v9.3 and have never played around with ODS before. I've been trying to read up on it but most of what I find here and elsewhere is pc related. A working complete set of SAS statements will get me started. Need to get to stacked bar charts, pie charts, legends, etc.

Or, all you mainframers out there, is there a better way to send metrics out of SAS? (HTML via ZFS file possible?)

I need to automate a mainframe metrics reporting process (SAS/MXG) that was written before I was even born!! I've got it up to the point of producing the charts/tables and sending via mainframe SMTP.

I have created cavedrawing bar charts in PROC CHART that are imbedded in the body of e-mails. I was hoping that once I got to SAS v9.3 I could convert using now base imbedded ODS statements.

Jeff

Super User
Posts: 19,829

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

You say ODS and Excel, but that's a bit general.

There's tagsets.excelxp and html and tableeditor and you can make your own. 

Tagsets.excelxp won't do charts, so you'll essentially be doing html or MSOFFICE2k instead.

SAS Super FREQ
Posts: 8,868

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

Hi:

  Isn't this the 2nd time around for this question?

https://communities.sas.com/message/140949#140949

  Here's the thing...all the stuff that people mentioned in your other post are still true:

1) only PROC EXPORT or the LIBNAME engine can make true binary Excel files -- but true, binary Excel files do NOT have embedded charts.

2) When you use ODS techniques to create files, you are creating ASCII text files. No matter where you create the files, what file extension you give the files or what application you use to open the files, you are creating ASCII text files.

3) ODS HTML, ODS MSOFFICE2K and ODS TAGSETS.TABLEEDITOR all create pure HTML of one form or another. Excel knows how to open HTML files - -so sometimes people name their HTML file with a .XLS file extension, thinking that they are creating a true, binary Excel file -- but they are not, there are merely "fooling" the Windows registry.

4) Images and HTML: When you use SAS/GRAPH or ODS GRAPHICS to make images and you use ODS HTML (ODS MSOFFICE2K or ODS TAGSETS.TABLEEDITOR), you are using HTML techniques to link the image file to the HTML file via an HTML <IMG> tag. When the browser or rendering application opens the HTML file, the image from the <IMG> tag is pulled into the rendering application.

5) So, when you try to email an HTML file, there is NO way to "embed" the images in the HTML file. HTML links to images with an <IMG> tag. If you send an HTML file to someone, even if you put the HTML into the body of the email, you will still have an <IMG> tag (and NOT an embedded image) to mail. It will be your responsibility to a) either put the image on a drive that the person can get to when they open the email or b) email the image with the mail.

6) When you use ODS TAGSETS.EXCELXP to create files for Excel, you are creating Spreadsheet Markup Language 2003 "flavor" of XML files (an ASCII text file), and, by Microsoft design, images are NOT supported in thse files. So when Reeza said that TAGSETS.EXCELXP won't do charts -- she meant that any graph output -- charts, graphs, bars, pies, scatter plots -- basically any image from SAS/GRAPH or ODS GRAPHICS will nto be able to be linked to for this destination.

7) The reason that PROC CHART's "cavedrawing" images are "embedded" are that PROC CHART uses simple keyboard letters, such as * and + and - in order to draw your charts. Sort of like you sat there on a typewriter and typed each character so they all lined up.

8) when you say that you are "looking for ODS code to create a simpel verticle bar chart in Excel, and then attach in an e-mail, all from a mainframe." you got some answers and links in your other posting that were all relevant to what you want to do. But basically, the bottom line is that you are NOT going to be able to embed the images in an Excel file using ODS. The best you can do is send HTML in the mail and send the images as attachments. Or, investigate whether creating an RTF file on the mainframe is possible.

  I think your best bet for help is to open a track with Tech Support on this question. Getting SAS 9.3 hasn't changed the information that you got in your earlier post.

cynthia

Frequent Contributor
Posts: 75

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

Posted in reply to Cynthia_sas

Cynthia,

This was a seperate question based off all the information you and other gave me in my previous post. I am able to chart simple data into the body of e-mails and send them via mainframe SMTP. Based off of your suggestion, I did open a track with SAS support, asking them if it is currently possible to embed HTML to the body of an e-mail (even under SAS v9.3). They informed me that it is currently not possible, nor is it a function they are pursuing in the near future. So that left me with other avenues to pursue if I wanted more than the simple charts I am currently producing. Automation is the underlying key to all of my questions, by the way.

So, off of others suggestions, I pursued creating HTML and XML files that were able to be opened by Microsoft Excel. I have never been able to accomplish this. I've found bits and pieces of code online and put together a "working" program that is creating an HTML file with a .XLSX extension (at multiple suggestions). And when I say working, I mean it produces an HTML file in the zfs directory I point it to. But to date I have not been able to successfully open the file. My direction with all this was to chart all the data, save to an HTML file that Excel could open, then send that as an attachment in an e-mail. This is what spawned my question for this post.

While playing around with the HTML, I  have been able to manage to create simple web pages with the charts imbedded and serve them directly off the zfs directories. With these I can send the links embedded in the body of an e-mail and just have the end users branch to the pages. Again, my underlying effort here is automation. I think this is possibly my best avenue at this point. Now my efforts are focused on sprucing up the charts. I want to be able to create stacked bar charts with "tables" containing the metrics underneath the charts. I am still using PROC CHART, PROC PLOT and PROC GBARLINE but am trying to read up on ODS statements.

I'm still a newbie to all this but am not giving up.

Super User
Posts: 19,829

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

Does the file get created, and you can't open it?

If you relocate the file do a windows desktop can you then open it?

Do you have excel installed on your server? It typically isn't as far as I know.

Frequent Contributor
Posts: 75

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

Reeza,

The file is created, with the .xlsx extension. I e-mail it to myself and the attachment opens under Excel. But the data is garbage, most likey due to a lack of conversion from EBCDIC to ASCII I'm assuming. Code examples I've found online claim you can produce Excel workbooks, complete with charts, all through HTML and open the file in Excel. So far I haven't been able to open a workbook with charts.

SAS Super FREQ
Posts: 8,868

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

Hi:

  Let me say again, that ODS HTML is never, never, never creating an Excel workbook. ODS HTML and all the HTML-based destinations are creating ASCII text files that can be opened in Excel and, when opened in Excel, will look like workbooks. But, the underlying truth is that HTML is HTML -- it is a language of markup tags that describe the presentation of the content. Images are referenced in an HTML <IMG> tag. To the extent that Microsoft knows what to do with HTML tags and IMG tags, then on a PC, for example (which already "talks" ASCII), I can create an HTML file with IMG tags and when Excel opens the HTML file, it renders the HTML tags as though my content was in a workbook.

  But, I have to do an explicit File--Save AS to save that file as a true, binary .XLS or .XLSX file. I have posted before several explanations of what an XLSX file really is (a zipped archive of multiple files) and so the only way to create a "true" XLSX file with SAS is to use the LIBNAME engine or PROC EXPORT -- and those methods do not allow graphics.

  There is an ODS destination called TAGSETS.TABLEEDITOR that will create an HTML file and puts a JavaScript button and some embedded JavaScript in the HTML tags to save the HTML as Excel -- but again -- you have to be able to run JavaScript for that destination to work and also, you usually MUST open the file in a browser first and then manually click the button to initiate the "write" to Excel format.

  You said you'd worked with Tech Support on this and I don't think there's any more definitive answer than the one you got from Tech Support.

cynthia

Super User
Posts: 19,829

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

What happens when you output the file using a .html extension and then email that file to yourself. Then open the file through excel, ie Excel>Open File> my_file.html?

Specifically the sample file below:

ods html file='Your location\test.html' style=meadow;

proc print data=sashelp.class;

run;

ods graphics on;

proc univariate data=sashelp.class;

run;

ods html close;

SAS Super FREQ
Posts: 8,868

Re: Mainframe code to create simple bar chart in Excel, attach in e-mail

Hi, Reeza: You are creating test.html, but opening my_file.html...was that a typo?

cynthia

also, if the OP is going to test this on the mainframe, a couple of other options are needed:

ODS HTML FILE='XXX.YYY.ZZZ.HTML' RS=NONE TRANTAB=ASCII;
  
...code...
  
ODS HTML CLOSE;

see this handout for more mainframe file creation tips, like DCB for the mainframe file.

http://support.sas.com/rnd/base/ods/templateFAQ/MVSODS3.pdf

However, the graphs created by ODS GRAPHICS will -still- use an IMG  tag. There is NO WAY around the fact that HTML makes an IMG tag. So when you email the HTML file, you will also have to email the IMAGE file. So, sadly, I think that your ODS GRAPHICS  step will need more information about where to write the IMG file. Really, I think the information that the OP got from Tech Support is the final word on this subject.

Ask a Question
Discussion stats
  • 8 replies
  • 1399 views
  • 0 likes
  • 3 in conversation