I have a SAS program that is creating a mainframe dataset that is in .xls format. I can transfer the dataset to my local drive, using binary, overwrite, and VB. I can open that file through Excel with no problems. The need is to be able to email the mainframe file as an .xls attachment. I AM receiving the email with the .xls attachment, but Excel is not able to successfully open the file. It opens with garbage. I have looked through the various posts on the Forums and have not found a sound solution for sending Excel files.
I am using IEBGENER to send my email. Here is the following IEBGENER code:
//*-----------------------------
//* EMAIL EXCEL WORKSHEET
//*-----------------------------
//STEP1 EXEC PGM=IEBGENER
//*
//SYSUT1 DD DSN=MYSMTP.CODE.COMMANDS,DISP=SHR
// DD DSN=MYSAS.EXCEL.FORMAT.DATA,DISP=SHR
// DD DSN=MYSMTP.CODE.PERIOD,DISP=SHR
//*
//SYSUT2 DD SYSOUT=(B,SMTP)
//*
//SYSOUT DD SYSOUT=X,OUTLIM=10000
//SYSPRINT DD SYSOUT=X,OUTLIM=10000
//SYSUDUMP DD SYSOUT=4
//*
//SYSIN DD DUMMY
//*
Each file in SYSUT1 is 500 bytes in length, VB.
My SMTP code looks as follows:
HELO MYSERVER.COM
MAIL FROM: <MYSELF@MYSELF.COM>
RCPT TO: <MYSELF@MYSELF.COM>
DATA
TO: <MYSELF@MYSELF.COM>
FROM: <MYSELF@MYSELF.COM>
SUBJECT: E-MAIL NOTIFICATION - XLS ATTACHED
MIME-VERSION: 1.0
CONTENT-TYPE: APPLICATION/VND.MS-EXCEL
CONTENT-DISPOSITION: ATTACHMENT; FILENAME=TEST.XLS
I have also tried adding Content-Transfer-Encoding: base64, binary, etc. However, nothing has been successful. Any helpful information would be greatly appreciated. I think that somehow the encoding through SMTP/MIME is turning my data to garbage, but I don't know what to do to prevent that from happening.
Again, I have searched through these Forums and have found no usable solution. I know that emailing through SAS is a an option, but the business need is to do this through SMTP if that is possible.
P.S. My server names, email ID's, and dataset names have been changed to generic names to protect the security of my company. Those are not the real names for my server, or dataset names stated in my examples. However, I do know that the ones that I am using are correct.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
Which version of SAS are you using? Recent versions of SAS include the ODS (Output Delivery System) which allows you to send an email directly from SAS. The code for a text attachment is
Code:
FILENAME TEMP EMAIL
TO=("RSAMPLE@xxx.COM" "other.user@xxx.COM")
SUBJECT='THIS IS A TEST'
TYPE='TEXT/HTML';
ODS HTML FILE=TEMP RS=NONE;
PROC PRINT DATA=<SAS Data Set>;
RUN;
ODS HTML CLOSE;
and you may need OPTIONS GROUP=EMAIL; in your SAS code.
I am not a SAS programmer. I believe my SAS version is: SAS (r) 9.1 (TS1M3). My background is COBOL, EZT. I have copied a similar SAS program used within my company and gutted it to produce a 1 row, 1 cell spreadsheet for testing purposes. The SAS code creates my dataset, but it does not send the email. My SAS code is as follows:
**********************************************************
* SAS PGM: SASPGM
*
* THIS PROGRAM FORMATS TO SEND 1 ROW
* 1 CELL SPREADSHEET TO MYSELF.
*
*
**********************************************************
OPTIONS OBS=MAX REPLACE COMPRESS=YES;
%MACRO SCRATCH(FILENAME);
DATA _NULL_;
RETAIN FILEREF "SCRATCH " ;
RC=FILENAME(FILEREF,"&FILENAME");
RCD=FDELETE(FILEREF);
RC=FILENAME(FILEREF," ");
IF RCD THEN PUT "NOTE: FILE &FILENAME WAS SCRATCHED";
ELSE PUT "WARNING: UNABLE TO SCRATCH FILE &FILENAME";
STOP;
RUN;
%MEND;
DATA _NULL_;
FILENAME DOEMAIL EMAIL
TO='MYSELF@MYSELF.COM'
FROM='MYSELF@MYSELF.COM'
SUBJECT=("THIS IS A TEST E-MAIL")
EMAILID='MYSELF@MYSELF.COM'
ATTACH=("MYSAS.EXCEL.FORMAT.DATA" CT='APPLICATION/MS-EXCEL'
NAME="SAS_TEST_DSN" EXT='XLS');
RUN;
Again, my business requirement is to email the SAS created file through SMTP, and not do it within SAS. I don't like reinventing the wheel, but my management sets the direction for my department. The SAS email portion is not working. Is there not a way to do this successfully through SMTP after my SAS file is cataloged? This is my question. I have been able to successfully send text email attachments through SMTP, but not Excel. I need to know if there is a solution for SMTP to send Excel formatted mainframe files and what paraments I need in my SMTP to make it successfully work.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Once a file is created, there should be no technical problem sending it via SMTP (assuming the file does not violate any "rules").
There are many examples in the forum of sending e-mail using an IEBGENER step or using XMITIP (a free e-mail process).
Suggest you look within your application(s) for some other job that sends and e-mail with an attachment.
Quote:
I have been able to successfully send text email attachments through SMTP, but not Excel. I need to know if there is a solution for SMTP to send Excel formatted mainframe files
When we send data to be imported into an Excel spreadsheet, we always send a delimited text file. If you are actually creating an Excel spreadsheet, i suspect that it will need to be sent as a binary file.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
Google is your friend. Googling sas ods excel returns over 1 million hits, and the first link (to SAS Institute's web site) includes a link to a paper presented at the SAS Global Forum 2012 titled An Introduction to Creating Multi-Sheet Microsoft Excel Workbooks the Easy Way with SASĀ®.
You seem to be using MIME for the email; you need to use the SAS method withFILENAME EMAIL and ods tagsets to create the output Excel file, then use ODS to email the output file.
This requires a fairly advanced understanding of SAS -- it is definitely NOT something someone with little or no experience in SAS should be attempting. Your best bet is probably to generate a comma-delimited (CSV) file and email that, start by creating the file and then in a separate job using ODS to email it. Once that works, then you can work on combining the two tasks.
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
Hi,
You could try to create the XLS file in USS and then send it as email using FTP. Creating a XLS/PDF in mainframe and sending it as email has been a pain to me as well and cant quite figure it out, so had to resort to USS method.
The below JCL may not be syntactically correct, since I do not have a mainframe to test :-( :-( *withdrawal symptoms kicking in*
You could use the basic idea a try out if it works.
Thanks for the further follow up, but I have never used USS and don't get the piece where you say to write the file to USS. Is there any JCL that would further show an example of writing a file to USS?
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
Hi,
Code:
FILENAME OUTHTML "/mysystem/report.xls";
This statement alone will write the file to Unix System Services which is under under Z/OS (provided you have access to USS directory - OMVS segment)
If you want to view the unix files you can use TSO ISHELL command.
no further JCL is required to write the file to Unix.
Code:
//INPUT DD *
ip address
userid
IP address can be obtained using TSO NETSTAT HOME command.
This userid and password would most likely be same as your mainframe userid and password.
I don't have access to the TSO ISHELL command. ISHELL is not found. I do have access to the TSO NETSTAT HOME command. What I still do not get is the JCL to execute the USS piece. I would need the EXEC statement and the DD's other than INPUT DD to be able to give this a try.
And, also how does the FILENAME OUTHTML "/mysystem/report.xls"; fit into the JCL. Does this write the file to a server file. It needs to be a mainframe file name, as we can't have production jobs writing to private servers.
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
Hello Paul,
Quote:
And, also how does the FILENAME OUTHTML "/mysystem/report.xls"; fit into the JCL. Does this write the file to a server file. It needs to be a mainframe file name, as we can't have production jobs writing to private servers.
It does not write the file in private servers. USS is part of your mainframe. The USERID and PASSWORD are your mainframe userid and password. *open to corrections.*
The FILENAME statement does not need to be coded on the JCL. It needs to be placed on the SAS program.
In one of your posts you have shown a SAS program like this
This writing to Unix thing is a convoluted approach and since you don't have much idea about it. I suggest you look and learn more information about USS before proceeding or abandon the whole USS idea and play with various SMTP options until you get it right. Good luck!