BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sklal
Obsidian | Level 7
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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 1467 views
  • 1 like
  • 2 in conversation