Hi sas user's,
Is it possible to do left join in proc sql and using macrovariables in on sentence?
I want to parametizer this code:
proc sql;
create table final as
select a.*, b.id_5,c.id_4,d.id_3, e.id_2, f.id_1
from one as a
left join aux5 as b
on a.var1=b.var1 and a.var2=b.var2 and a.var3=b.var3 and a.var4=b.var4
and a.var5=b.var5
left join aux4 as c
on a.var1=b.var1 and a.var2=b.var2 and a.var3=b.var3 and a.var4=b.var4
left join aux3 as d
on a.var1=b.var1 and a.var2=b.var2 and a.var3=b.var3
left join aux2 as e
on a.var1=b.var1 and a.var2=b.var2
left join aux1 as f
on a.var1=b.var1;
quit;
I think i can create a list with macrovariables, something like that
%let list_var = var1 var2 var3 var4 var5;
But i don't know how to use it in on sentence of proc sql.
I can use data step union too.
Someone can help?
Thanks!
Yes, a macro ought to work here.
UNTESTED CODE
%macro dothis;
proc sql;
create table final as
a.*,
%do i=5 %to 1 %by -1;
aux&i..id_&i %if &i>1 %then %str(,);
%end;
from one as a
%do i=5 %to 1 %by -1;
left join aux&i on
%do j=1 %to &i;
a.var&j=aux&j..var&j %if &j<&i %then %str(and);
%end;
%end;
;
quit;
%mend;
%dothis
Yes, a macro ought to work here.
UNTESTED CODE
%macro dothis;
proc sql;
create table final as
a.*,
%do i=5 %to 1 %by -1;
aux&i..id_&i %if &i>1 %then %str(,);
%end;
from one as a
%do i=5 %to 1 %by -1;
left join aux&i on
%do j=1 %to &i;
a.var&j=aux&j..var&j %if &j<&i %then %str(and);
%end;
%end;
;
quit;
%mend;
%dothis
I'm trying it, thanks about your reply.
The problem is my vars not always have same pattern then I was thinking about create a list and iterate it.
Here how you can iterate over a list.
%macro demo(list);
%let i=1;
%do %while(%scan(&list,&i) ne );
%put %scan(&list,&i);
%let i=%eval(&i+1);
%end;
%mend;
%demo(var1 var2 var3 var4 var5);
Or a variation:
%macro demo(list);
%if &list ne %then
%do i=1 %to %sysfunc(countw(&list));
%put %scan(&list,&i);
%end;
%mend;
%demo(var1 var2 var3 var4);
Just loop over an INDEX into the list of names.
%let var_list = var1 var2 var3 var4 var5;
%do index=1 %to %sysfunc(countw(&var_list,%str( )));
%let var=%scan(&var_list,&index,%str( ));
....
%end;
I leave it as an exercise how to generate the code you want using loop(s) like above.
Hint: You don't have to use A B C as aliases, you can use alias like A1 A2 ...
You could use the ut_sql_equijoin macro that I posted to generate the conditions and potentially %ut_varlist to generate the select statements.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.