topic Re: how to provide tab name/sheet name while sending excel report with JCL SAS program in SAS Programming

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

thirupathicob — Tue, 30 Jan 2024 14:38:39 GMT

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.

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

andreas_lds — Tue, 30 Jan 2024 14:53:59 GMT

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.

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

Kurt_Bremser — Tue, 30 Jan 2024 14:58:22 GMT

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.

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

thirupathicob — Tue, 30 Jan 2024 15:15:16 GMT

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.

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

thirupathicob — Tue, 30 Jan 2024 15:23:57 GMT

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')

________

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;

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

Tom — Tue, 30 Jan 2024 15:30:54 GMT

@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.

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

thirupathicob — Wed, 31 Jan 2024 03:47:30 GMT

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

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

Tom — Wed, 31 Jan 2024 04:03:46 GMT

@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.

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

thirupathicob — Wed, 31 Jan 2024 06:44:10 GMT

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.

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;

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

Patrick — Wed, 31 Jan 2024 07:13:29 GMT

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?

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

thirupathicob — Wed, 31 Jan 2024 07:51:29 GMT

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.

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

Kurt_Bremser — Wed, 31 Jan 2024 08:24:35 GMT

@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.

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

thirupathicob — Wed, 31 Jan 2024 10:43:20 GMT

Thank you so much for your help kurt.