Mail result of different tests

[Max-A]

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?

[Kurt_Bremser]

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;

[SuryaKiran]

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.

[Max-A]

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.

[Kurt_Bremser]

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.

[Max-A]

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

[Kurt_Bremser]

The open code %if-%then is one of the really nice-to-have things in 9.4M5.

[Max-A]

I probably missed something, because no mail is send, but the log is not showing any errors.

[Kurt_Bremser]

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;

[Max-A]

Does the file Mailbox statement support running without the %. Now i receive an error when using the file statement. ("Statement is not valid or it is used out of proper order.")

[SuryaKiran]

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;

[Max-A]

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.

[Kurt_Bremser]

The into clause in SQL must create the same variable you use in the condition.

[Max-A]

Thanks! nearly there. The only thing that is not working atm is the file statement. It looks like this is not the way to use it in a macro. Any ideas?

[Kurt_Bremser]

From your log, the file statement is never even compiled because the macro fails.