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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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