topic Re: use > symbol in where condition within macro in SAS Programming

use > symbol in where condition within macro

Ronein — Sun, 05 Aug 2018 08:59:12 GMT

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??*/

Re: use > symbol in where condition within macro

Kurt_Bremser — Sun, 05 Aug 2018 09:01:53 GMT

Because you end up with this invalid code:

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

Re: use > symbol in where condition within macro

Ronein — Sun, 05 Aug 2018 10:31:07 GMT

But in your answer you didn't use macro.

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

Re: use > symbol in where condition within macro

akash1088 — Sun, 05 Aug 2018 11:06:21 GMT

%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));

Re: use > symbol in where condition within macro

Kurt_Bremser — Sun, 05 Aug 2018 11:30:20 GMT

@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!

Re: use > symbol in where condition within macro

PaigeMiller — Sun, 05 Aug 2018 11:42:53 GMT

@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?

Re: use > symbol in where condition within macro

FreelanceReinh — Sun, 05 Aug 2018 13:11:10 GMT

@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)

Re: use > symbol in where condition within macro

Ronein — Sun, 05 Aug 2018 12:35:16 GMT

Thank you very much,

It is exactly what I wanted.