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: Call for Content

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!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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