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??*/
@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)
Because you end up with this invalid code:
PROC SQL;
create table outcome_6 as
select *
from tbl
where branch in (>0)
;
QUIT;
But in your answer you didn't use macro.
My question was how to use the condition >0 under macro (with a macro varaible)
@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!
@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?
@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)
Thank you very much,
It is exactly what I wanted.
%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));
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!
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.