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.
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 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.