BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PreenaKaur
Calcite | Level 5

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 ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

8 REPLIES 8
BrunoMueller
SAS Super FREQ

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

PreenaKaur
Calcite | Level 5

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.

BrunoMueller
SAS Super FREQ

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

PreenaKaur
Calcite | Level 5

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)

 

PreenaKaur
Calcite | Level 5

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?

 

 

 

BrunoMueller
SAS Super FREQ

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

PreenaKaur
Calcite | Level 5

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! 

 

 

BrunoMueller
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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