send an email from sas after doing some data validation

[tparvaiz]

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

[Reeza]

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. 

[s_lassen]

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.

[tparvaiz]

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;

[Reeza]

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. 

[tparvaiz]

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

[tparvaiz]

any feedback?

[Reeza · Accepted solution]

Solution #2 from my initial answer is still valid.