DATA Step, Macro, Functions and more

how to send emails to recipients extracted from the dataset

Reply
Frequent Contributor
Posts: 129

how to send emails to recipients extracted from the dataset

Hi,

 

I am using following code to generate email. 

 

 

----------------------------------------------------------------CODE-------------------------

 


%macro email;
FILENAME Mailbox EMAIL 'xyz@hotmail.com'
Subject='Report - Ran okay' ;
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please find the Report as an attachment";
PUT "Thank you";
PUT " ";
RUN;

%mend email;
run;

 

%macro email_error;
FILENAME Mailbox EMAIL 'xyz@hotmail.com'
Subject='Report - Error' ;
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please be advised that Report was generated with potential errors";
PUT "Thank you";
PUT " ";
RUN;

%mend email_error;
run;

 


data Table2a_;
set Table2(where = (
location_ID = 14
) );
if 180000 <= price <= 300000 then call execute ('%email');else call execute ('%email_error');
run;

 

------------------------------------------------------

may I know how to use email addresses from a dataset rather than defining it in the code. for example, let's say I have a dataset TBL_EMAIL that looks something like this

 

Report_ID  email_address

1                 abc@hotmail.com

1                 xyz@hotmail.com

2                 abc@hotmail.com

 

so I want to send the report to all the email addresses where report id = 1

 

please advise

 

Thanks

Trusted Advisor
Posts: 1,129

Re: how to send emails to recipients extracted from the dataset

this is an untested code, hopefully will work

 

%macro email(emailid);
FILENAME Mailbox EMAIL "&emailid"
Subject='Report - Ran okay' ; 
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please find the Report as an attachment";
PUT "Thank you";
PUT " ";
RUN;
%mend email;

%macro email_error(emailid);
FILENAME Mailbox EMAIL "&emailid"
Subject='Report - Error' ;
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please be advised that Report was generated with potential errors";
PUT "Thank you";
PUT " ";
RUN;
%mend email_error;

data Table2a_;
set Table2(where = (
location_ID = 14 
) );
if 180000 <= price <= 300000 then call execute ('%email('||email_address||')');else call execute ('%email_error('||email_address||')');
run;
Thanks,
Jag
Frequent Contributor
Posts: 129

Re: how to send emails to recipients extracted from the dataset

How is it calling TBL_EMAIL.EMAIL_ADDRESS

 

can you please advise

Trusted Advisor
Posts: 1,129

Re: how to send emails to recipients extracted from the dataset

We need to use the dataset which has the email id

 

data Table2a_;
set Table2(where = (
location_ID = 14 
) );
if 180000 <= price <= 300000 then call execute ('data _null_; set TBL_EMAIL; where report_id=1; %email('||email_address||'); run;');else call execute ('data _null_; set TBL_EMAIL; where report_id^=1;%email_error('||email_address||');run;');
run;
Thanks,
Jag
Frequent Contributor
Posts: 129

Re: how to send emails to recipients extracted from the dataset

Here is the code that failed and generated an error message

 

 

---------------------CODE that I used - --------------------------------------------------

 

 

data Table2a_;
set Table2(where = (
location_ID = 14
) );
if 180000 <= price <= 300000
then call execute ('data _null_; set TBL_EMAIL; where report_id=1; %email('||email_address||'); run;');
else call execute ('%email_error');
run;

 

 

----------------Error Message that I got ------------------------

 

NOTE: The file MAILBOX is:
E-Mail Access Device

ERROR: Invalid E-mail recipient.

 

-----------------------What worked ------------------------------

 

Following code worked (suggesting that there is something wrong in a way I am calling the email macro)

 

data Table2a_;
set Table2(where = (
location_ID = 14 
) );
if 180000 <= price <= 300000 
then call execute ('%email(myemail@Domain.com)');
else call execute ('%email_error');
run;

 

 

 

Thanks

Frequent Contributor
Posts: 129

Re: how to send emails to recipients extracted from the dataset

here is how I am creating a test table

 


data TBL_EMAIL;
input

nuid $ 1-7
email_address $ 9-28
report_id $ 32-33
objectname $ 34-47
objecttype $ 49-64;
cards;
A111111 myemail@Domain.com 1 test report report
A111112 myemail@Domain.com 2 test report report
;
run;

 

Ask a Question
Discussion stats
  • 5 replies
  • 115 views
  • 0 likes
  • 2 in conversation