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 ?
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
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
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.
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
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)
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?
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
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!
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.