IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Email Mainframe SAS Excel File As Attachment Through SMTP


IBM Mainframe Forums -> All Other Mainframe Topics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Thu Sep 27, 2012 11:42 pm
Reply with quote

Hi,

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.

Thanks!
Back to top
View user's profile Send private message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Thu Sep 27, 2012 11:46 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu Sep 27, 2012 11:51 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Fri Sep 28, 2012 12:35 am
Reply with quote

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;

LIBNAME TMP1 '&SASTMP1' SPACE=(CYL,(15,1));
LIBNAME TMP2 '&SASTMP2' SPACE=(CYL,(5,5));

DATA TMP1.WRK;
INFILE FILEN010;
INPUT
@001 EXT_SOURCE $1.
@002 FILLER $CHAR79.
;
RUN;

DATA TMP2.WRK;
INFILE FILEN020 ;
INPUT
@01 COMPANY $CHAR1.
@02 FILLER $CHAR79.
;
RUN;

PROC TEMPLATE;
DEFINE STYLE STYLES.NOBORDER;
PARENT=STYLES.MINIMAL;
STYLE TABLE /
BORDERWIDTH = 1.0;
END;
RUN;

TITLE;RUN;

%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;

%SCRATCH(MYSAS.EXCEL.FORMAT.DATA);

FILENAME OUTHTML "'MYSAS.EXCEL.FORMAT.DATA'"
DISP=(NEW,CATLG,DELETE)
UNIT=SYSDA
SPACE=(CYL,(1,1),RLSE)
LRECL=500 BLKSIZE=0;

ODS MSOFFICE2K BODY=OUTHTML(URL="'MYSAS.EXCEL.FORMAT.DATA'")
HEADTEXT='<STYLE> .TEXT{MSO-NUMBER-FORMAT:\@;}</STYLE>'
STYLE=STYLES.NOBORDER RS=NONE TRANTAB=ASCII;
OPTIONS NODATE ORIENTATION=LANDSCAPE LS=256;

PROC REPORT DATA=TMP1.WRK NOWINDOWS MISSING CENTER HEADLINE HEADSKIP

STYLE={FONT_FACE='ARIAL'}
STYLE(HEADER)={FONT_WEIGHT=BOLD FONT_FACE='ARIAL' FONT_SIZE=10PT
VJUST=MIDDLE BACKGROUND=DARKBLUE FOREGROUND=WHITE};
COLUMN EXT_SOURCE;

DEFINE EXT_SOURCE /DISPLAY 'EXTRACT SRCE' CENTER
STYLE(COLUMN)={HTMLSTYLE='MSO-NUMBER-FORMAT:000000000'};
RUN;
ODS MSOFFICE2K CLOSE;
RUN;

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;

My execution JCL looks like this:

//*-----------------------------------------------------
//* PROGRAM TO EMAIL EXCEL WORKSHEET
//*-----------------------------------------------------
//STEP1 EXEC SAS,
// OPTIONS='EMAILSYS=SMTP EMAILHOST=MYSERVER.COM'
//*
//INCLUDE DD DSN=MYCOMPANY.PROD1.SAS,DISP=SHR
// DD DSN=MYDSN.PDS,DISP=SHR
// DD DSN=MYCOMPANY.PROD1.CARDLIB,DISP=SHR
//*
//FILEN010 DD *
X
/*
//*
//FILEN020 DD *
A
//*
//SYSIN DD DSN=MYDSN.PDS(SASPGM),DISP=SHR
//*
//SASLIST DD SYSOUT=X,OUTLIM=10000
//*

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.

Thanks!
Back to top
View user's profile Send private message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Fri Sep 28, 2012 12:37 am
Reply with quote

P.S. My tech support group here at my company has not been of much help.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Sep 28, 2012 1:51 am
Reply with quote

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.
Back to top
View user's profile Send private message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Fri Sep 28, 2012 2:49 am
Reply with quote

I have tried several methods of sending the .xls attachment. Examples of what I have tried are below:

mime-version: 1.0
content-disposition: attachment; filename=sas.xls
content-type: application/octet-stream
content-transfer-encoding: base64

mime-version: 1.0
content-disposition: attachment; filename=sas.xls
content-type: application/vnd.ms-excel
content-transfer-encoding: binary

mime-version: 1.0
content-disposition: attachment; filename=sas.xls
content-type: application/vnd.ms-excel
content-transfer-encoding: base64

None of these have worked.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Sep 28, 2012 3:11 am
Reply with quote

Hello,

If you pull the sas.xls file from the mainframe to your desktop using ftp, will it successfully load into Excel?

Can you post this small test file using HEX ON?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Fri Sep 28, 2012 4:56 am
Reply with quote

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.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Tue Oct 02, 2012 10:40 pm
Reply with quote

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.

1. Change
Code:
FILENAME OUTHTML "'MYSAS.EXCEL.FORMAT.DATA'"
DISP=(NEW,CATLG,DELETE)
UNIT=SYSDA
SPACE=(CYL,(1,1),RLSE)
LRECL=500 BLKSIZE=0;

to
Code:
FILENAME OUTHTML "USS FILENAME HERE";

This will write the file to USS provided you have access.

2. Change the email sending IEBGENER step to FTP step.
Code:
//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

to
Code:
//STEP1 EXEC PGM=FTP
//SYSPRINT DD SYSOUT=*                                                         
//OUTPUT   DD SYSOUT=*   
//SMTPOUT DD SYSOUT=(B,SMTP)                                                       
//SMTPSTMT DD *
 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/OCTET-STREAM
CONTENT-DISPOSITION: ATTACHMENT; FILENAME=TEST.XLS
/*
//INPUT    DD *
  ip address
  userid
  password
  TYPE E                                                                         
  MODE S                                                                               
  PUT //DD:SMTPSTMT //DD:SMTPOUT
  PUT 'USS FILENAME HERE' //D:SMTPOUT
  QUIT
/*


Hope it helps.
Back to top
View user's profile Send private message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Tue Oct 02, 2012 11:30 pm
Reply with quote

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?
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Tue Oct 02, 2012 11:48 pm
Reply with quote

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.

Hope it helps.
Back to top
View user's profile Send private message
Paul D Hunter

New User


Joined: 27 Sep 2012
Posts: 7
Location: USA

PostPosted: Wed Oct 03, 2012 12:04 am
Reply with quote

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.

Basically, I would need:

//STEP1 EXEC ?
//*
//INPUT DD *
ip address
userid
/*

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.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Wed Oct 03, 2012 12:23 am
Reply with quote

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
Code:
FILENAME OUTHTML "'MYSAS.EXCEL.FORMAT.DATA'"
DISP=(NEW,CATLG,DELETE)
UNIT=SYSDA
SPACE=(CYL,(1,1),RLSE)
LRECL=500 BLKSIZE=0;


This part needs to be replaced by
Code:
FILENAME OUTHTML "/mysystem/report.xls";

on the SAS program.

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!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> All Other Mainframe Topics

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Mainframe openings in Techmahnidra fo... Mainframe Jobs 0
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top