The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

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 ?

 


Accepted Solutions
Solution
‎07-26-2016 03:20 AM
SAS Super FREQ
Posts: 703

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

Hi

 

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 703

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

Hi

 

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

Occasional Contributor
Posts: 11

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

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

 

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.

Solution
‎07-26-2016 03:20 AM
SAS Super FREQ
Posts: 703

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

Hi

 

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

Occasional Contributor
Posts: 11

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

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)

 

Occasional Contributor
Posts: 11

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

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?

 

 

 

SAS Super FREQ
Posts: 703

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

Hi

 

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

Occasional Contributor
Posts: 11

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

[ Edited ]

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! 

 

 

SAS Super FREQ
Posts: 703

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 497 views
  • 0 likes
  • 2 in conversation