Generate an automatic email after the sql query condition

[Sathish_jammy]

I have to run the below query weekly once. If the record_cnt > 0 then it needs to send an automatic email to lead.

proc sql;
select count(*) into :record_cnt
 from table1 where id not in (select id from table2);

I have tried the below code, however it not worked well.

data _null;
%if &record_cnt > 0 %then %do;
filename msg email 
 to="lead@core.com"
 from="replies-disabled@company.com"
 subject = "New record_found";

data _null_;
file msg;
 put "%trim(&record_cnt.) record found.";
end;
run;

Kindly suggest the appropriate solution,

Thanks, in advance!

[sbxkoenk]

Make sure SMTP e-mail server is set up!

Then look here:

• Home > SAS Communities Library > How to send email using SAS?
  Started ‎06-10-2021 | Modified ‎08-04-2022
  https://communities.sas.com/t5/SAS-Communities-Library/How-to-send-email-using-SAS/ta-p/746523
• How to use Gmail to send a message from a SAS program?
  By Chris Hemedinger on The SAS Dummy July 31, 2013
  https://blogs.sas.com/content/sasdummy/2013/07/31/gmail-from-sas-program/

Then do something like this, but write to mail and not to print destination!

%LET record_cnt=7;

%IF &record_cnt.>0 %THEN %DO;
data _null_;
file print;
 record_cnt=strip(&record_cnt.);
 put record_cnt 'record found.';
run;
%END;
/* end of program */

Koen

[Patrick]

What do you mean by "not worked well"?

In the code you shared make sure the Proc SQL ends with a QUIT;

The first "data _null_;" statement doesn't make sense and should get removed.

I assume the missing macro %end statement just got "cut off" when you copy/pasted your code?

[Sathish_jammy · Accepted solution]

Thanks for your suggestions @sbxkoenk and @Patrick

The below one works.

proc sql;
select count(*) into :record_cnt
 from table1
 where id not in (select id from table2);
%let count=&record_cnt;
quit;

%macro test;
%if &count. > 0 %then %do;
filename msg email 
 to="leademail@company.com"
 from="myemail@company.com"
 subject = "Alert: New Record Found";

data _null_;
 file msg;
 put "Hi,";
 put "%trim(&count.) new records found in the dataset. Please take action.";
 %end;
run;
%mend;
%test

[Patrick]

@Sathish_jammy I strongly recommend to amend your code as per the highlighted lines below. 

If your condition &count>0 is false then your macro will currently still generate a run; statement.

As for the SQL the following code would suffice and populate macro var &record_cnt the same as your code.

proc sql;
  select count(*) into :record_cnt trimmed
    from table1
      where id not in (select id from table2);
quit;