BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
liguang01
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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
--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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
--
Paige Miller
liguang01
Obsidian | Level 7

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.

Patrick
Opal | Level 21

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

 

Tom
Super User Tom
Super User

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

DavePrinsloo
Pyrite | Level 9

You could use the ut_sql_equijoin macro  that I posted to generate the conditions and potentially %ut_varlist  to generate the select statements.  

 

SAS Innovate 2025: Register Now

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!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1124 views
  • 3 likes
  • 5 in conversation