use > symbol in where condition within macro

[Ronein]

Hello

Why this code is not working?

I want to use condition of all branches >0

Data tbl;
input ID  branch;
cards;
1 100
2 100
3 200
4 300
5 400
6 500
7 500
8 500
9 500
10 500
;
run;

%macro rjoe(x,branchP);
PROC SQL;
	create table outcome_&x. as
	select *	   
	from tbl
	where branch in (&branchP.)
;
QUIT;
%mend;

%rjoe(x=1,branchP=100);
%rjoe(x=2,branchP=200);
%rjoe(x=3,branchP=300);
%rjoe(x=4,branchP=400);
%rjoe(x=5,branchP=500);
%rjoe(x=6,branchP=100 200 300 400 500); 
%rjoe(x=5,branchP=>0);/*It is  not working .why??*/

[Kurt_Bremser]

Because you end up with this invalid code:

PROC SQL;
	create table outcome_6 as
	select *	   
	from tbl
	where branch in (>0)
;
QUIT;

[Ronein]

But in your answer you didn't use macro.

My question was how to use the condition >0  under macro (with a macro varaible)

[Kurt_Bremser]

---

@Ronein wrote:

But in your answer you didn't use macro.

My question was how to use the condition >0  under macro (with a macro varaible)

---

I resolved the macro manually to show you the resulting code, and why it can't work. This is not a macro problem, but a case of forcing the macro to create crappy code. Since you can't use >0 in the in-list in base SAS, you also can't do it via the macro.

Once again, for the umpteenth time:

Get working base SAS code before you make it dynamic with a macro!

[PaigeMiller]

---

@Ronein wrote:

But in your answer you didn't use macro.

My question was how to use the condition >0  under macro (with a macro varaible)

---

Agreeing with @Kurt_Bremser, write working SAS code first. Then you can much more easily turn it into a macro.

I gave a solution in your other thread. Does it make sense? (And please don't post the same question twice)

MODERATOR: can you combine these threads into one?

[FreelanceReinh · Accepted solution]

@Ronein: In practice it's more common to pass the entire WHERE condition to the macro: 

where &branchP

(in this case you'd better use a more generic name like cond for this parameter since it is no longer limited to conditions on variable branch).

This requires more typing in the macro calls than previously, e.g. 

%rjoe(7, branch in (200,500))

But the benefit is that you now have unlimited flexibility regarding the WHERE condition. Examples: 

%rjoe(8, 200<=branch<=400)
%rjoe(9, branch in (select 25*age from sashelp.class))
%rjoe(10, .<branch<300 or id=8)
%rjoe(11, (id=8 or .<branch<300))

The latter example shows that sometimes additional parentheses (or macro quoting functions) are necessary to avoid misinterpretations on the part of the macro processor (here: id=... would look as if there was a keyword parameter id).

Addendum: With this technique it's also very easy to code an "empty" condition (which amounts to a PROC SQL step without a WHERE clause):

%rjoe(12, 1)

[Ronein]

Thank you very much,

It is exactly what I wanted.

[akash1088]

%rjoe(x=5,branchP=>0); 

 Because in where condition you are using in operator and while calling macro you are giving value '>0' which is invalid.

 when Macro get resolves  the where condition look like this

where branch in (>0) 

which is invalid 

you can modify the code like this.

%macro rjoe(x,branchP);
PROC SQL;
 create table outcome_&x. as
 select * 
 from tbl
 where branch &branchP.
;
QUIT;
%mend;

%rjoe(x=1,branchP=in (100);
%rjoe(x=2,branchP=in (200));
%rjoe(x=3,branchP=in (300));
%rjoe(x=4,branchP=in (400));
%rjoe(x=5,branchP=in (500));
%rjoe(x=6,branchP=in (100 200 300 400 500)); 
%rjoe(x=5,branchP=>0));