topic Re: Sending email with filtered excel attachments from sas in SAS Programming

Sending email with filtered excel attachments from sas

sklal — Wed, 04 Apr 2018 08:30:23 GMT

Hi all,

I am required to send filtered excel attachment to different email ids from sas. For eg I have a column named region in the excel having 4 values - north,south,east, west and i have to send the northern region data to id xyz@jkl.com , southern region data to abc@jkl.com and so on. How should I go about filtering the excel via sas programming? Kindly help. Also, please suggest how to insert multiple recipients for which the filtering condition would be different.I'm attaching the code written on SAS Enterprise Guide 7.1 for single recipient which sends the unfiltered excel.

options emailsys = smtp
emailid = 'xxxx@gmail.com'
emailpw = 'xxxx'
emailhost = 'smtp.gmail.com'
emailport = 587 ;

options emailhost=
(
'smtp.gmail.com'
STARTTLS auth=LOGIN

id='xxxx@gmail.com'

pw='xxxx' port=587
)
;
filename f_email email
to = 'xxxx@jkl.com'
from = 'xxxx@gmail.com'
subject = 'Test'
attach=("xxxx.xlsx"  content_type="application/xlsx");

data _null_;
file f_email;
put 'Test';
run;

Regards,

sklal

Re: Sending email with filtered excel attachments from sas

Kurt_Bremser — Wed, 04 Apr 2018 08:45:38 GMT

Wrap your code into a macro and call it off a control dataset:

%macro send_mail(email,attachment);

filename f_email email
to = "&email"
from = 'xxxx@gmail.com'
subject = 'Test'
attach=("&attachment"  content_type="application/xlsx");

data _null_;
file f_email;
put 'Test';
run;

%mend;

data control;
infile cards dlm=',';
input email :$30. attachment :$50.;
cards;
xxxx@jkl.com,xxxx.xlsx
;
run;

data _null_;
set control;
call execute('%send_mail(' !! trim(email) !! ',' !! trim(attachment) !! ');');
run;

Re: Sending email with filtered excel attachments from sas

sklal — Wed, 04 Apr 2018 09:15:24 GMT

Thanks Kurt for your response, I'm able to send the mail to multiple recipients now, but how should i go about filtering the column(Eg - Region) inside the excel in the program itself? Also, what if I want to send different filtered excel to different email ids?

Re: Sending email with filtered excel attachments from sas

Kurt_Bremser — Wed, 04 Apr 2018 11:49:56 GMT

Move a selecting step and the creation of the Excel (still better would be a csv) into the macro, and add a macro parameter for the condition. Add the same to the control dataset and the call execute.

The list of recipients in a filename email can be separated by blanks, BTW.

Re: Sending email with filtered excel attachments from sas

sklal — Wed, 04 Apr 2018 12:21:17 GMT

Thanks Kurt, I think I got what you're suggesting (I'm a newbie). I'll try to implement the same.

Re: Sending email with filtered excel attachments from sas

Kurt_Bremser — Wed, 04 Apr 2018 12:42:10 GMT

If you run into trouble, post code and log, and where the result differs from what you expected (unless you have ERRORs, which are obvious).