Sending email with filtered excel attachments from sas

[sklal]

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

[Kurt_Bremser]

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;

[sklal]

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? 

[Kurt_Bremser · Accepted solution]

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.

[sklal]

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

[Kurt_Bremser]

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).