how to provide tab name/sheet name while sending excel report with JCL SAS program

[thirupathicob]

DATA REPORT;

FORMAT DATE1 MMDDYY8.;

CALL SYMPUT ('HDATE',PUT(TODAY(),MMDDYY8.));

FORMAT EMAILDATE MMDDYY8.;

PUT "!EM_SUBJECT!" "HDATE11";

FILENAME MYFILE EMAIL

FROM= (KG12456@ABC.COM)

TO= (KG12456@ABC.COM)

ATTACH=("USER.FGHJ.MKINM5XR.XCVX.ELVRPT"

              NAME='SPLIT_CONTRACT_RPT' EXTENSION='XLS')

TYPE= "TEXT/PLAIN";

TAB= "SHEET1";

DATA REPORT;

SET REPORT;

FILE MYFILE;

Error is coming here for TAB statement. I want to work on multiple tabs in single excelsheet. Thanks in advance.

[andreas_lds]

Please post properly formatted code using the "insert sas code" button (little running man), also post the complete log, so that we see the error message and its context, actually.

EDIT: It looks as if you have >> tab="sheet1" << in the middle of nowhere. This won't work. You have to provide the sheet-name while creating it.

[thirupathicob]

Thank you for the reply. 

I have to create excelfile with a sheet name. How can I define sheet name. Please shed some light. Here is the error code.

1          DATA REPORT;

2          FORMAT DATE1 MMDDYY8.;

3          CALL SYMPUT ('HDATE',PUT(TODAY(),MMDDYY8.));

4          FORMAT EMAILDATE MMDDYY8.;

5          PUT "!EM_SUBJECT!" "HDATE11";

6          FILENAME MYFILE EMAIL

7          FROM= (KG12456@ABC.COM)

8          TO= (KG12456@ABC.COM)

9          ATTACH=("USER.FGHJ.MKINM5XR.XCVX.QWERTY"

10                       NAME='ALTNET_NEW_CONT_RPT' EXTENSION='TXT'

11                 "USER.FGHJ.MKINM5XR.XCVX.ELVRPT"

12                       NAME='SPLIT_CONTRACT_RPT' EXTENSION='XLS'

13                       SHEET_NAME='SHEET1')

                                    ________

                                    24

ERROR 24-2: Invalid value for the ATTACH option.

14         TYPE= "TEXT/PLAIN";

ERROR: Error in the FILENAME statement.

2                                                          The SAS System

NOTE: Variable DATE1 is uninitialized.

NOTE: Variable EMAILDATE is uninitialized.

!EM_SUBJECT!HDATE11

NOTE: The data set WORK.REPORT has 1 observations and 2 variables.

NOTE: The DATA statement used 0.01 CPU seconds and 19991K.

NOTE: The address space has used a maximum of 876K below the line and 23676K abo

15         DATA REPORT;

16         SET REPORT;

17         FILE MYFILE;

[Kurt_Bremser]

FILENAME is a global statement, and should never be part of DATA or PROC step code.

Your FILENAME is syntactically wrong, the values for the FROM= and TO= options have to be in quotes:

filename myfile email
  from= "KG12456@ABC.COM"
  to= "KG12456@ABC.COM"
  attach=(
    "USER.FGHJ.MKINM5XR.XCVX.ELVRPT"
    name='SPLIT_CONTRACT_RPT'
    extension='XLS'
  )
  type= "text/plain"
;

The sheet names in the Excel file must be defined when you create it, not when you attach it to the email.

Your DATA steps will only create a dataset with a single observation containing only missing values.

[thirupathicob]

Thank you for the reply. This is my mainframe dataset "USER.FGHJ.MKINM5XR.XCVX.ELVRPT". I am trying to attach this file as excel.

I have to create excelfile with a sheet name. How can I define sheet name. Please shed some light.

[Tom]

---

@thirupathicob wrote:

Thank you for the reply. This is my mainframe dataset "USER.FGHJ.MKINM5XR.XCVX.ELVRPT". I am trying to attach this file as excel.

I have to create excelfile with a sheet name. How can I define sheet name. Please shed some light.

---

So what is USER.FGHJ.MKINM5XR.XCVX.ELVRPT?  Is that a SAS dataset?  (There is confusion because mainframe folks talk about normal files as datasets).  If not then what type of file is it.  Is it already an XLS file?  If so the names of any sheets that it might contain is already set and you cannot change the sheet names just be attaching the file to an email.

If it is NOT an XLS file then you will need to first convert whatever it is into an XLS (or better an XLSX) file.  During that process you can assign a name to the sheet (or sheets) that you create in it.  Then attach that new file to your email.

[thirupathicob]

It is already in Mainframe XLS format. We dont have facility to define tab name under mainframe dataset.

[Tom]

---

@thirupathicob wrote:

It is already in Mainframe XLS format. We dont have facility to define tab name under mainframe dataset.

---

You are using that ambiguous terminology again, "dataset".   An XLS file is not a dataset.  It is just a file that uses a special binary format.  So if the file is an XLS file then you don't have any SAS datasets.

EMAIL has no method to change the internal content of a file.  All you can do with a file is attach it to the email. 

If you want to make a new XLS file then you could use different name(s) for the worksheet(s) in the file.  But if you tried to use SAS to do that you would probably lose any special formatting of the cells in the worksheet that might exist.

[thirupathicob]

Thank you so much for your time on this post. I have a final question with two scenarios

scenario1: I got email with two excel sheets attached. It worked as expected.

DATA REPORT;
FORMAT DATE1 MMDDYY8.;
CALL SYMPUT ('HDATE',PUT(TODAY(),MMDDYY8.));
FORMAT EMAILDATE MMDDYY8.;
PUT "!EM_SUBJECT!" "HDATE11";
FILENAME MYFILE EMAIL
FROM= ("KG12456@ABC.COM")
TO= ("KG12456@ABC.COM")
ATTACH=("USER.GHJK.KKL0M5XR.AMKX.NEWCNTRT"
NAME='REPORT1' EXTENSION='XLS'
"USER.GHJK.KKL0M5XR.AMKX.OLDCNTRT"
NAME='REPORT2' EXTENSION='XLS')
TYPE= "TEXT/PLAIN";
DATA REPORT;
SET REPORT;
FILE MYFILE;

scenario2: I got errors though I have used same files which I used in scenario1. It has keep two files in single excel with tabs. I have highlighted error in bold.

DATA REPORT;
FORMAT DATE1 MMDDYY8.;
CALL SYMPUT ('HDATE',PUT(TODAY(),MMDDYY8.));
FORMAT EMAILDATE MMDDYY8.;
PUT "!EM_SUBJECT!" "HDATE11";
FILENAME MYFILE EMAIL
FROM= ("KG12456@ABC.COM")
TO= ("KG12456@ABC.COM")
ATTACH=("USER.GHJK.KKL0M5XR.AMKX.NEWCNTRT"
NAME='REPORT1' EXTENSION='XLS'

SHEET_NAME='TAB1'

                                 ______

                                  24
ERROR 24-2: Invalid value for the ATTACH option.
"USER.GHJK.KKL0M5XR.AMKX.OLDCNTRT"
NAME='REPORT1' EXTENSION='XLS'

SHEET_NAME='TAB2')
TYPE= "TEXT/PLAIN";
DATA REPORT;
SET REPORT;
FILE MYFILE;

[Patrick]

You never ever only opened or saved an Excel sheet on its own but you always work with an Excel Workbook. Such a workbook can have one or more sheets (tabs). 

From outside the file is the workbook and that's what you can send as email attachment.

If you have an existing Excel Workbook with multiple sheets but you only want to send a Workbook with one of the sheets then you need to create this new Workbook with only one sheet. For this you need software than can read and write Excel workbooks. On a mainframe (z/OS) your options are limited.

"Google" tells me that there is now a Python compiler and interpreter available IBM Open Enterprise SDK for Python and Python package openpyxl would allow you to create a new Excel workbook and then copy a sheet from another existing workbook to this new workbook ....but that's for .xlsX and not the "outdated" binare .xls format. I strongly assume that for doing something similar with .xls you would need a Windows operating system using vbs or powershell.

Questions:

Who created this .xls in the first place and how did it get onto the Mainframe? 

Would it be possible to change the program that creates the .xls so it only includes the sheets you want to send via email?

[thirupathicob]

Questions:

Who created this .xls in the first place and how did it get onto the Mainframe?  Ans: Above mentioned SAS has created excel.

Would it be possible to change the program that creates the .xls so it only includes the sheets you want to send via email?

Ans: Above mentioned SAS has created excel.

[Kurt_Bremser · Accepted solution]

---

@thirupathicob wrote:

Questions:

Who created this .xls in the first place and how did it get onto the Mainframe?  Ans: Above mentioned SAS has created excel.

Would it be possible to change the program that creates the .xls so it only includes the sheets you want to send via email?

Ans: Above mentioned SAS has created excel.

---

None of the codes you posted creates an Excel file.

Excel files can be created from SAS with a LIBNAME pointing to an Excel file with one of the Excel engines, with PROC EXPORT, or with ODS EXCEL. Your codes contain none of these.

Please post the code which creates the Excel file you try to attach.

[thirupathicob]

Thank you so much for your help kurt.