topic Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS in ODS and Base Reporting

Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

PreenaKaur — Mon, 25 Jul 2016 09:42:21 GMT

Hi All,

I am trying to :

Create an excel sheet using output of my 4 proc sql statements

then email it

add email body

First 2 are working fine however when I am trying to put email body I am getting two different emails , my code is as follows:

proc sql;

CREATE TABLE t1 AS

select * from abc;

run;

proc sql;

CREATE TABLE t2 AS

select * from def;

run;

proc sql;

CREATE TABLE t3 AS

select * from ghi;

run;

proc sql;

CREATE TABLE t4 AS

select * from jkl;

run;

ods TAGSETS.EXCELXP body=outbox rs=none style=styles.HTMLBlue;

ods TAGSETS.EXCELXP text="test sample text";

ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="abc");

proc print data=t1 noobs;

run;

ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="def");

proc print data=t2 noobs;

run;

ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="ghi");

proc print data=t3 noobs;

run;

ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="jkl");

proc print data=t4 noobs;

run;

ods TAGSETS.EXCELXP close;

filename outbox email

to='abc@abc.com'

from='def@def.com'

type='application/vnd.ms-excel'

subject='Sample First Program';

DATA _null_;

PUT 'Please find attached the report';

PUT ' ';

PUT 'Thankyou';

RUN;

I have seen so many similar threads but none of them are working for me. I tried ODS text , separate Data _null_ option, tried putting all into one filename option.

Can you please help in identifying the issue in this ?

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

BrunoMueller — Mon, 25 Jul 2016 10:24:54 GMT

First create the file that is used as an attachment to the email, this is what you did.

The DATA step that writes the body of the email, needs a FILE statement, so that the something is written to the body contents.

For SAS9.4 there is a EXCEL ODS destination that can be used.

Here is sample code that will create an excel file to be used as the attachment.

The DATA Step then writes the body of the email and attaches the excel file create previously .

filename tmpxlsx temp;
ods excel file=tmpxlsx options(sheet_name="Female");

proc print data=sashelp.class;
  where sex = "F";
run;

ods excel options(sheet_name="Male");

proc print data=sashelp.class;
  where sex = "M";
run;

ods excel close;

filename outbox email

  to='abc@def.com'

  from='abc@def.com'
  attach=(
    "%sysfunc(pathname(tmpxlsx))"
    name="someName"
    ct='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    ext="xlsx"
  ) 

  subject='Sample First Program'
;

DATA _null_;
  file outbox;
  PUT 'Please find attached the report';
  PUT ' ';
  PUT 'Thankyou';
RUN;

filename tmpxlsx clear;
filename outbox clear;

Bruno

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

PreenaKaur — Mon, 25 Jul 2016 11:04:21 GMT

seems its easier in 9.4 however i am still on 9.3 only 😞

I got the following error when trying to run the code :

ERROR: Unable to load module 'SpreadsheetML' from template store!
ERROR: No body file. EXCEL output will not be created.

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

BrunoMueller — Mon, 25 Jul 2016 11:23:13 GMT

The use this code sample, change excel to use tagsets.excelxp also changed the extension to XML.

filename tmpxlsx temp;
ods tagsets.excelxp file=tmpxlsx options(sheet_name="Female");

proc print data=sashelp.class;
  where sex = "F";
run;

ods tagsets.excelxp options(sheet_name="Male");

proc print data=sashelp.class;
  where sex = "M";
run;

ods tagsets.excelxp close;

filename outbox email

  to='abc@def.com'

  from='abc@def.com'
  attach=(
    "%sysfunc(pathname(tmpxlsx))"
    name="someName"
    ct='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    ext="xml"
  ) 

  subject='Sample First Program'
;

DATA _null_;
  file outbox;
  PUT 'Please find attached the report';
  PUT ' ';
  PUT 'Thankyou';
RUN;

filename tmpxlsx clear;
filename outbox clear;

Bruno

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

PreenaKaur — Tue, 26 Jul 2016 07:22:02 GMT

Thanks a lot Bruno !

Its working for me now ,hope this will work when running from DI (as a batch run) as well ! (I am checking for this)

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

PreenaKaur — Mon, 08 Aug 2016 05:01:56 GMT

Hi Bruno,

This program works very smoothly from enterprise guide however when I schedule this (in overnight batch run) I am not getting any email . I think its related to some of my access but i am not sure which access it is? (DR_GSD_USER_BATCH referes to batch name)

ERROR: Insufficient authorization to access /opt/sas/env5/lev1/config/sascompute/Lev1/SASApp/DR_GSD_USER_BATCH.lst.

Any ideas please?

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

BrunoMueller — Tue, 09 Aug 2016 06:46:55 GMT

the name of the file implies, that you where using a relative file name and the the user does not have the right to write to the full path.

If you need a filename where you can always write, I suggest to use the follwoing approach:

write the the file to the location, where the WORK library is pointing too.

The code below shows a way to do this.

%let tempFile = %sysfunc(pathname(work))/sample.lst;

%put NOTE: &=tempFile;

Bruno

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

PreenaKaur — Thu, 11 Aug 2016 05:30:25 GMT

Hi Bruno,

I did the changes and now not getting previous error message but still there is no email and no error message in the log.

My code was as follows:

filename tmpxlsx temp;
%let tmpxlsx = %sysfunc(pathname(work))/sample.lst;
%put NOTE: &=tmpxlsx;

ods tagsets.excelxp file=tmpxlsx options(sheet_name="Female");
proc print data=sashelp.class;
where sex = "F";
run;

ods tagsets.excelxp options(sheet_name="Male");
proc print data=sashelp.class;
where sex = "M";
run;

ods tagsets.excelxp close;

filename outbox email
to='abc@abc.com'
from='def@def.com'
attach=(
"%sysfunc(pathname(tmpxlsx))"
ct='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
ext="xls"
)

subject='Sample Third Program'
;

DATA _null_;
file outbox;
PUT 'Please find attached the report';
PUT ' ';
PUT 'Thankyou';
RUN;

filename tmpxlsx clear;
filename outbox clear;

seems file is created but not in work library:

MPRINT(READSAS): filename outbox email to='abc@abc.com' from='def@def.com' attach=(
"/sasworka/env5/lev1/SAS_work038300001E1E_mg5g02n01a/#LN00026"
ct='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ext="xls" ) subject='Sample Third Program' ;
MPRINT(READSAS): DATA _null_;

Apologies If I am doing something wrong, I am fairly new to SAS!

Re: Receiving 2 separate emails when trying to put email body in excel sheet generated using ODS

BrunoMueller — Fri, 12 Aug 2016 14:37:25 GMT

I assume that for batch the ODS LISTING destination is open.

So as the first line in your code, add the following:

ODS _ALL_ CLOSE;

This will close any open destinations, and only the ones you have in your code are used.

Bruno