topic Re: PROC SQL or DATA STEP union with list of var in "on" condition in SAS Procedures

PROC SQL or DATA STEP union with list of var in "on" condition

liguang01 — Thu, 19 Mar 2020 10:22:02 GMT

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!

Re: PROC SQL or DATA STEP union with list of var in "on" condition

PaigeMiller — Thu, 19 Mar 2020 11:16:40 GMT

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

Re: PROC SQL or DATA STEP union with list of var in "on" condition

liguang01 — Thu, 19 Mar 2020 13:34:33 GMT

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.

Re: PROC SQL or DATA STEP union with list of var in "on" condition

Tom — Thu, 19 Mar 2020 13:42:57 GMT

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

Re: PROC SQL or DATA STEP union with list of var in "on" condition

Patrick — Thu, 19 Mar 2020 13:54:14 GMT

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

Re: PROC SQL or DATA STEP union with list of var in "on" condition

DavePrinsloo — Sat, 21 Mar 2020 21:00:52 GMT

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