I am trying to create a test which will send the results by email.
I created the mail already, but i want to add a condition to it.
The thing it has to check is whether a table contains missing values in certain columns. (col1 and col2 for example).
I was hoping there would be an easy way to call the column of a table and use the
"if WORK.MYTABLE.col1=. then" Statement.
Unfortunately this is not working, is there another way to check whether the columns of a table contain any missing values?
Remove '%' symbol before RUN. It has to be just 'run' not '%run'
Please find my comments below:
proc sql;
select case when nmiss(name)=0 and nmiss(age)=0 and nmiss(sex)=0 then 1
else 0 end into:testMacro
from work.test;
quit;
%macro mail;
FILENAME Mailbox EMAIL
to="test@email.com" /* TO= is missing */
type="text/html"
subject="Automatische controle";
%put "why isnt it working";
data _null_;
file Mailbox;
/* You maynot need the 'if' condition anymore */
/*%if &testMacro = 0*/
/*%then %do;*/
/* %put "values in mailcond are > 0";*/
/*%end;*/
run;
%mend mail;
data _null_;
if &testMacro = 0 then call execute('%mail');
run;
See a quick example here:
data test;
set sashelp.class;
if _n_ = 5 then age = .;
run;
proc sql;
select nmiss(age) into :mailcond
from test;
quit;
%if &mailcond
%then %do;
%put sending mail here;
%end;
Note:
@Kurt_Bremser example uses open code %IF %THEN which is supported only if your using latest SAS release SAS 9.4 Maintenance 5. For lower versions you may need to put within a macro.
proc sql;
select nmiss(name), nmiss(age), nmiss(sex) into :mailcond
from test;
quit;
FILENAME Mailbox EMAIL "mail@test.nl" type="text/html";
filename REPORT "%sysfunc(pathname(work))\test.html";
data _null_;
file Mailbox;
if &mailcond > 0
then do;
put "values in mailcond are > 0";
end;
run;
the problem now is that the program will always send a mail. Which is not too much of a problem of course, i just have to add a final put to say that everything is fine. But is there a way to only send the email if &mailcond > 0? Because the put "values in mailcond are > 0"; is working already.
Wrap the mail sending code in a macro condition:
%if &mailcond
%then %do;
filename mailbox email "mail@test.nl" type="text/html";
data _null_;
file Mailbox;
put "values in mailcond are > 0";
run;
%end;
If you are before SAS 9.4M5, you need to put the whole %if %then block into a macro definition and call the macro.
nothing happens with the new code:
%Macro mail();
%if &mailcond
%then %do;
FILENAME Mailbox EMAIL "" type="text/html";
data _null_;
file Mailbox;
put "values in mailcond are > 0";
run;
%end;
%mend;
%mail();
RUN; QUIT;
i do not use 9.4. i am currently on 9.3, hopefully we will update to 9.4 in a few months
The open code %if-%then is one of the really nice-to-have things in 9.4M5.
I probably missed something, because no mail is send, but the log is not showing any errors.
Use %put statements at suitable places in your code to verify that you get the values in your macro variables that you expect.
Also run macro code with
options mlogic mprint symbolgen;
You might need to change the condition on checking all three variables missing. In your previous code you selected 3 columns and put only one macro variable.
proc sql;
select case when nmiss(name)=0 and nmiss(age)=0 and nmiss(sex)=0 then 1
else 0 end into:Flag
from test;
quit;
Later you can call the macro using call execute.
%macro send_email();
FILENAME Mailbox EMAIL "mail@test.nl" type="text/html";
filename REPORT "%sysfunc(pathname(work))\test.html";
data _null_;
file Mailbox;
if &mailcond > 0
then do;
put "values in mailcond are > 0";
end;
run;
%mend send_email;
data _null_;
if &Flag=0 then call execute('%send_email');
run;
When i execute the code below:
proc sql;
select case when nmiss(name)=0 and nmiss(age)=0 and nmiss(sex)=0 then 1
else 0 end into:testMacro
from work.test;
quit;
%Macro mail();
FILENAME Mailbox EMAIL "" type="text/html";
data _null_;
file Mailbox;
%if &mailcond
%then %do;
%put "values in mailcond are > 0";
%end;
run;
%mend mail;
data _null_;
if &testMacro = 0 then call execute('%mail');
run;
i receive the error below
MPRINT(MAIL): FILENAME Mailbox EMAIL "" type="text/html";
"why isnt it working"
MPRINT(MAIL): * data _null_;
MPRINT(MAIL): file Mailbox;
WARNING: Apparent symbolic reference MAILCOND not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&mailcond
ERROR: The macro MAIL will stop executing.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
2 The SAS System 16:29 Wednesday, October 31, 2018
NOTE: CALL EXECUTE generated line.
1 + FILENAME Mailbox EMAIL "" type="text/html"; * data _null_; file Mailbox;
NOTE: Line generated by the CALL EXECUTE routine.
1 + FILENAME Mailbox EMAIL "" type="text/html"; * data _null_; file Mailbox;
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
The into clause in SQL must create the same variable you use in the condition.
From your log, the file statement is never even compiled because the macro fails.
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.