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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.