*sql;
proc sql;
create table want1 as
select * from
(select a.a1,
a.a2,
a.y1,
b.y2
from table1 a left join table2 b
on a.a1=b.a1 and a.a2=b.a2
union
select b.a1,
b.a2,
a.y1,
b.y2
from table1 a right join table2 b
on a.a1=b.a1 and a.a2=b.a2)
where a1 in (select distinct a1 from table1);
;
quit;
*hash;
data want2;
length a1 $8 a2 8 y1 y2 $8;
if _N_=1 then do;
if 0 then set table2;
dcl hash h(dataset:"table2",multidata:"y")
x(multidata:"y")
a(multidata:"y",ordered:"y")
u();
h.definekey("a1","a2");
h.definedata("a1","a2","y2");
h.definedone();
x.definekey("a1","a2");
x.definedata("a1","a2","y1");
x.definedone();
a.definekey("a1","a2");
a.definedata("a1","a2","y1","y2");
a.definedone();
u.definekey("a1");
u.definedone();
dcl hiter hi("h") ai("a");
end;
do until(last);
set table1 end=last;
x.add();
_iorc_=u.add();
_iorc_=h.find();
if _iorc_ ne 0 then call missing(y2);
a.replace();
end;
if last;
do while(hi.next()=0);
_iorc_=x.find();
if _iorc_ ne 0 then call missing(y1);
a.replace();
end;
if last;
do while(ai.next()=0);
if u.find()=0 then output;
end;
h.clear();
x.clear();
a.clear();
u.clear();
run;
... View more
