topic Re: Joins in Macro application in SAS Programming

Joins in Macro application

BrahmanandaRao — Mon, 30 Nov 2020 11:30:02 GMT



data t1;
input id;
cards;

1
2
3
4
5
;
run;

data t2;
input id;
cards;
5
6
7
8
;
run;



%macro left(left,right ,var);
proc sql;
select *
from &left as a
left join &right as b 
on a.&var=b.&var;
quit;
%mend;

%left(t1,t2,id);

Here I am trying to join table with macro application

how to define joins for

left join

right join

full join

inner join

need help me

above code is correct

Re: Joins in Macro application

PeterClemmensen — Mon, 30 Nov 2020 11:43:14 GMT

Why do you want to do this? The SAS macro language is for generating code. Why not just write the joins with no macro?

And if this is for practice, why do you want the answer rather than trying yourself?

Re: Joins in Macro application

BrahmanandaRao — Mon, 30 Nov 2020 11:49:23 GMT

i tried but how to use left ,right inner joins using macro application see above code

Re: Joins in Macro application

PaigeMiller — Mon, 30 Nov 2020 11:55:42 GMT

@BrahmanandaRao wrote:

Here I am trying to join table with macro application

how to define joins for

left join

right join

full join

inner join

need help me

above code is correct

Is the question that you want the code to do something other than a left join?

You can simply add another argument into the macro definition that will specify the type of join.

Re: Joins in Macro application

BrahmanandaRao — Mon, 30 Nov 2020 12:10:36 GMT

data t1;
input id;
cards;

1
2
3
4
5
;
run;

data t2;
input id;
cards;
5
6
7
8
;
run;



%macro abc(left,right ,var,j_type);
proc sql;
select *
from &left as a
&j_type  &right as b 
on a.&var=b.&var;
quit;
%mend;

%abc(t1,t2,id,right_join);

Re: Joins in Macro application

Kurt_Bremser — Mon, 30 Nov 2020 12:33:21 GMT

Post logs as text, NOT as pictures!!
The problem is PAINFULLY obvious, please use your own brain for once.

Re: Joins in Macro application

PaigeMiller — Mon, 30 Nov 2020 13:22:00 GMT

Your macro, when all the macro variables resolve, must produce working valid legal SAS code — and it does not produce working valid legal SAS code. That's what the log is showing you. A very trivial change fixes this.

If it's still not obvious, then you need to go back to the first step in macro writing, which is to produce code that works (in this case for your right join) without macros and without macro variables. Once you have this working, you should be able to turn it into a macro that works. If you can't produce working SAS code without macros and without macro variables, then it will never work when you add macros and add macro variables.