BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Max-A
Obsidian | Level 7

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran

View solution in original post

17 REPLIES 17
Kurt_Bremser
Super User

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
Meteorite | Level 14

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.

Thanks,
Suryakiran
Max-A
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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

Max-A
Obsidian | Level 7

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

Kurt_Bremser
Super User

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
Obsidian | Level 7
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
Meteorite | Level 14

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;
Thanks,
Suryakiran
Max-A
Obsidian | Level 7

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.
Max-A
Obsidian | Level 7
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?

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 1743 views
  • 5 likes
  • 3 in conversation