topic Re: Mail result of different tests in SAS Programming

Mail result of different tests

Max-A — Wed, 31 Oct 2018 13:29:02 GMT

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?

Re: Mail result of different tests

Kurt_Bremser — Wed, 31 Oct 2018 13:39:43 GMT

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;

Re: Mail result of different tests

SuryaKiran — Wed, 31 Oct 2018 14:04:22 GMT

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.

Re: Mail result of different tests

Max-A — Wed, 31 Oct 2018 14:10:33 GMT

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.

Re: Mail result of different tests

Kurt_Bremser — Wed, 31 Oct 2018 14:15:12 GMT

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.

Re: Mail result of different tests

Max-A — Wed, 31 Oct 2018 14:40:08 GMT

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

Re: Mail result of different tests

Kurt_Bremser — Wed, 31 Oct 2018 14:37:40 GMT

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

Re: Mail result of different tests

Max-A — Wed, 31 Oct 2018 14:41:55 GMT

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

Re: Mail result of different tests

Kurt_Bremser — Wed, 31 Oct 2018 14:46:39 GMT

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;

Re: Mail result of different tests

SuryaKiran — Wed, 31 Oct 2018 15:14:49 GMT

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;

Re: Mail result of different tests

Max-A — Wed, 31 Oct 2018 15:38:28 GMT

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.

Re: Mail result of different tests

Max-A — Wed, 31 Oct 2018 16:04:35 GMT

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.")

Re: Mail result of different tests

Kurt_Bremser — Wed, 31 Oct 2018 16:29:40 GMT

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

Re: Mail result of different tests

Max-A — Wed, 31 Oct 2018 16:34:15 GMT

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?

Re: Mail result of different tests

Kurt_Bremser — Wed, 31 Oct 2018 17:01:10 GMT

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

Re: Mail result of different tests

Max-A — Thu, 01 Nov 2018 10:39:48 GMT

1                                                          The SAS System                           11:33 Thursday, November 1, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program_newMaill';
4          %LET _CLIENTPROJECTPATH='R:\Mijn Documenten\Stagedocumenten\Eigen project\Prototypes\AutoTest_new.egp';
5          %LET _CLIENTPROJECTNAME='AutoTest_new.egp';
6          %LET _SASPROGRAMFILE=;
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
12       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE
12       !  GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
13         
14         GOPTIONS ACCESSIBLE;
15         data _null_;
16         if &testMacro = 1 then call execute('%mail');
17         run;

MPRINT(MAIL):   FILENAME Mailbox EMAIL "" type="text/html" subject="Automatische controle";
"why isnt it working"
MPRINT(MAIL):   * data _null_;
MPRINT(MAIL):   file Mailbox;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: CALL EXECUTE generated line.
1         + FILENAME Mailbox EMAIL ""  type="text/html" subject="Automatische controle"; *  data _null_; file 
Mailbox;
NOTE: Line generated by the CALL EXECUTE routine.
1         + FILENAME Mailbox EMAIL ""  type="text/html" subject="Automatische controle"; *  data _null_; file
                                                                                                                             ____
                                                                                                                             180
1        !+Mailbox;
ERROR 180-322: Statement is not valid or it is used out of proper order.

18         
19         
20         GOPTIONS NOACCESSIBLE;
21         %LET _CLIENTTASKLABEL=;
22         %LET _CLIENTPROJECTPATH=;
23         %LET _CLIENTPROJECTNAME=;
24         %LET _SASPROGRAMFILE=;
25         
26         ;*';*";*/;quit;run;
27         ODS _ALL_ CLOSE;
28         
29         
30         QUIT; RUN;
31

The error still remains, i just don't get what is wrong . I tried %file Mailbox. But that is not the solution either

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"
subject="Automatische controle";
%put "why isnt it working";

data _null_;
file Mailbox;
%if &testMacro = 0
%then %do;
	%put "values in mailcond are > 0";
%end;
%run;
%mend mail;

data _null_;
if &testMacro = 1 then call execute('%mail');
run;

Re: Mail result of different tests

SuryaKiran — Thu, 01 Nov 2018 12:40:29 GMT

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;

Re: Mail result of different tests

Kurt_Bremser — Thu, 01 Nov 2018 19:42:06 GMT

When you call execute a macro that contains macro logic, use %nrstr:

call execute('%nrstr(%mail'));

otherwise you will experience macro timing problems. Macro statements are executed immediately, but Base SAS code has to wait until the calling datastep ends. %nrstr prevents premature execution of macro logic.