DATA Step, Macro, Functions and more

send an email from sas after doing some data validation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 134
Accepted Solution

send an email from sas after doing some data validation

Hi,

 

I need to send an email from sas after doing some data validation

 

as an example, I have this table (table_A)

 

Region Location Value_total
A X 715
A Y 967
B Z 729
C AA 894
Total ABC 3305

 

------------------------here is my macro ---------------

 

%macro email;

FILENAME Mailbox EMAIL 'abc@cnn.com'
Subject='Test Mail message' ATTACH="C:\file.xlsx";
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Plesae find Report as an attachment";
PUT "Thank you";
RUN;

 

%mend email;

 

 

------------here is the psudo logic to call the macro ---------------------

I want to call the macro if Value_total is between 2000 and 4000, but don't know how to check the value from the table

 

would like to do something like this

 

data _null_;
if work.table1.Region = Total and work.table1.Location = 'ABC' and work.table1.Value_total is betwwn 2000 and 4000

then 

%email;

run;

 

 

please advise

 


Accepted Solutions
Solution
‎05-25-2017 02:06 PM
Super User
Posts: 19,770

Re: send an email from sas after doing some data validation

Solution #2 from my initial answer is still valid. 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: send an email from sas after doing some data validation

 

1. Either create macro variables for the values you need to drive this

2. Use the data step and if there are records or etc that meet your condition, cal the macro with CALL EXECUTE. You're not passing parameters, but the second example in CALL EXECUTE documentation is probably what you're looking for. 

 

Or there's an example in the SAS Macro Appendix that shows how to conditionally process if a dataset is empty. 

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

 

I think example 9 or 13 could be modified to work with your situation. 

 

 

 

PROC Star
Posts: 102

Re: send an email from sas after doing some data validation

I do not think you need a macro for that, how about

FILENAME Mailbox EMAIL 'abc@cnn.com' Subject='Test Mail message' ATTACH="C:\file.xlsx";
DATA _NULL_;
  set work.table1;
  where Region='Total' and Location='ABC' and Value_total between 2000 and 4000;
  FILE Mailbox;
  PUT "Hello";
  PUT "Please find Report as an attachment";
  PUT "Thank you";
  Stop;
RUN;

- the FILE and PUT statements never get executed if there are no data that comply with the WHERE clause.

Frequent Contributor
Posts: 134

Re: send an email from sas after doing some data validation

Here is what I've tried and it's generating an email, which shouldn't be the case as the value_total for region 4 is 90000 and the where clause criteria is between 180000 and 190000... please advise

 

data temp_data;
input Region Value_total;
DATALINES;
1 60000
2 70000
3 80000
4 90000;
run;

 


FILENAME Mailbox EMAIL 'abc@cnn.com' Subject='Test Mail message' ATTACH="C:\file.xlsx";

DATA _NULL_;
set work.temp_data;
where Region = 4 and Value_total between 180000 and 190000;
FILE Mailbox;
PUT "Hello";
PUT "Please find Report as an attachment";
PUT "Thank you";
Stop;
RUN;

 

 

Super User
Posts: 19,770

Re: send an email from sas after doing some data validation

Do you want to evaluate the condition and send an email for each row?

 

If so, you need either CALL EXECUTE() to call a macro or create the code for the email. Conditional execution is going to need a macro AFAIK, or you at least need to condition each statement. 

 

 

 

 

 

Frequent Contributor
Posts: 134

Re: send an email from sas after doing some data validation

Hi,

 

I want to send only one email.

 

 

to give some background. I have a table that has region IDs and associated revenues that they have generated.

 

want to send an email if the revenue for Region 4 is between a given range

 

Thanks

Frequent Contributor
Posts: 134

Re: send an email from sas after doing some data validation

any feedback?

Solution
‎05-25-2017 02:06 PM
Super User
Posts: 19,770

Re: send an email from sas after doing some data validation

Solution #2 from my initial answer is still valid. 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 151 views
  • 1 like
  • 3 in conversation