I am using the following code to send email through SAS if any of the datasets(daily) are missing after checking if it is a working day or not based on the working day dataset. Everything works but it sends email also even if wday_cnt = 0 (means non-working day). Can anybody let me know what I am doing wrong..
proc sql noprint;
/** check if it is a working day or not *************/
select count(new_date) into :wday_cnt
from wday_dsn
where date1 = today();
/***** Check if dsn2 data exists for today or not **************/
select count(distinct var1) into :dsn1_var_cnt
from dsn1
where dsn1_date = today();
/***** Check if dsn2 data exists for today or not **************/
select count(distinct var1) into :dsn2_var_cnt
from dsn2
where dsn2_date = today();
quit;
%put wday_cnt=&wday_cnt.;
%put dsn1_var_cnt =&dsn1_var_cnt.;
%put dsn2_var_cnt=&dsn2_var_cnt.;
filename mail email from="myname@email.com";
data _null_;
if &wday_cnt. = 1 then do;
if &dsn1_var_cnt. = 0 and &dsn2_var_cnt. > 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN1 data missing"; end;
if &dsn1_var_cnt. > 0 and &dsn2_var_cnt. = 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN2 data missing"; end;
if &dsn1_var_cnt. = 0 and &dsn2_var_cnt. = 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN1 and DSN2 data missing"; end;
end; /****** Working day loop end ***********/
run;
Can anybody help me??
Hi,
What happens if you put it into a macro, e.g.:
%macro testmail;
%if &wday_cnt. = 1 %then %do;
filename mail email from="myname@email.com";
data _null_;
if &dsn1_var_cnt. = 0 and &dsn2_var_cnt. > 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN1 data missing"; end;
if &dsn1_var_cnt. > 0 and &dsn2_var_cnt. = 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN2 data missing"; end;
if &dsn1_var_cnt. = 0 and &dsn2_var_cnt. = 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN1 and DSN2 data missing"; end;
run;
%end; /****** Working day loop end ***********/
Regards,
Amir.
Message was edited by: Amir - added macro call.
Thanks Amir for help. Yes, macro did the trick
I get the email notification also with just subject ="Report Alert" if &wday_cnt = 1 and &dsn1_var_cnt > 0 and &dsn2_var_cnt. I do not want this email notification. please help
proc sql noprint;
/** check if it is a working day or not *************/
select count(new_date) into :wday_cnt
from wday_dsn
where date1 = today();
/***** Check if dsn2 data exists for today or not **************/
select count(distinct var1) into :dsn1_var_cnt
from dsn1
where dsn1_date = today();
/***** Check if dsn2 data exists for today or not **************/
select count(distinct var1) into :dsn2_var_cnt
from dsn2
where dsn2_date = today();
quit;
%put wday_cnt=&wday_cnt.;
%put dsn1_var_cnt =&dsn1_var_cnt.;
%put dsn2_var_cnt=&dsn2_var_cnt.;
%macro testmail;
%if &wday_cnt. = 1 %then %do;
filename mail email from="myname@email.com";
data _null_;
if &dsn1_var_cnt. = 0 and &dsn2_var_cnt. > 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN1 data missing"; end;
if &dsn1_var_cnt. > 0 and &dsn2_var_cnt. = 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN2 data missing"; end;
if &dsn1_var_cnt. = 0 and &dsn2_var_cnt. = 0 then do;
file mail
to=("myname@email.com")
cc=("xyz@email.com" "abc@email.com")
subject="Report Alert";
put " DSN1 and DSN2 data missing"; end;
run;
%end; /****** Working day loop end ***********/
%mend testmail;
%testmail;
Anybody know how this can be resolved..
I resolved it.
I used the following condition in the loop
%if &wday_cnt. = 1 and (&dsn1_var_cnt. = 0 or &dsn2_var_cnt. = 0) %then %do;
%end;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.