BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tparvaiz
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Solution #2 from my initial answer is still valid. 

View solution in original post

7 REPLIES 7
Reeza
Super User

 

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
Meteorite | Level 14

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

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

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

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

Reeza
Super User

Solution #2 from my initial answer is still valid. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 1009 views
  • 1 like
  • 3 in conversation