DATA Step, Macro, Functions and more

Sending email with filtered excel attachments from sas

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Sending email with filtered excel attachments from sas

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


Accepted Solutions
Solution
‎04-09-2018 05:08 AM
Super User
Posts: 9,594

Re: Sending email with filtered excel attachments from sas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,594

Re: Sending email with filtered excel attachments from sas

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Sending email with filtered excel attachments from sas

Posted in reply to KurtBremser

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? 

Solution
‎04-09-2018 05:08 AM
Super User
Posts: 9,594

Re: Sending email with filtered excel attachments from sas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Sending email with filtered excel attachments from sas

Posted in reply to KurtBremser

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

Super User
Posts: 9,594

Re: Sending email with filtered excel attachments from sas

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 90 views
  • 1 like
  • 2 in conversation