topic macro in the where clause in SAS Programming

macro in the where clause

jffeudo86 — Thu, 02 May 2019 21:33:28 GMT

Hi,

Can I use a macro variable that has dynamic contents in a where clause. Please see below and pardon my limited knowledge of SAS/macro, Please ignore syntax errors and missing semicolons, if any. But I guess you know where I'm going with this.

%macro xx(cond);

proc sql;

select *

from table1

where &cond;

quit;

%mend xx;

%xx('col1 > 3 and col2 - col3 >= 10');

basically my question is around the where clause. Thank you!

Re: macro in the where clause

Jagadishkatam — Thu, 02 May 2019 22:00:29 GMT

It does work, i tested on sashelp.class

%macro xx(cond);
proc sql;
select * 
from sashelp.class
where &cond;
quit;
%mend xx;

%xx(age>10 and weight<60)

Re: macro in the where clause

heffo — Thu, 02 May 2019 22:31:29 GMT

You should not have quotation around your in parameter.

%xx(col1 > 3 and col2 - col3 >= 10);

Also, to see what is happening with the code, run the following before you do anything else. Should of course only be used during development, not in production.

options mprint mlogic symbolgen ;

In the log you will then see (if you run the code from jffeudo86):

MLOGIC(XX): Beginning execution.
MLOGIC(XX): Parameter COND has value age>10 and weight<60
MPRINT(XX): proc sql;
SYMBOLGEN: Macro variable COND resolves to age>10 and weight<60
MPRINT(XX): select * from sashelp.class where age>10 and weight<60;
MPRINT(XX): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

Compare to your initial code it would look like this:

SYMBOLGEN: Macro variable COND resolves to 'age>10 and weight<60'
MPRINT(XX): select * from sashelp.class where 'age>10 and weight<60';

Anything that is not 0 is true, so all rows will be selected (because your string is not 0).

Everything in macro is a string, so you have a string with quotation in it. Similar to a data step variable with the content

data x;
 var1 ="   'col1 > 3 and col2 - col3 >= 10'   "; *Spaces added for clarity.;
run;

Re: macro in the where clause

Astounding — Thu, 02 May 2019 22:46:09 GMT

As you have seen, the answer to your question is, "Of course you can."

One of the skills involved in writing macros is making them flexible. In this case, should the user be allowed to select all the observations, with no sub setting? In that case, how would you remove the word "where" from the program? That's just one thing you would learn along the way.

Re: macro in the where clause

jffeudo86 — Fri, 03 May 2019 12:50:09 GMT

Thank you all for your replies. They are all helpful. I chose this answer for the extra help in seeing what's going on in the background/log. And also the mention of the quotes. No wonder I am getting all the rows instead of those that only meet the condition.