Hello, I want to make a join between a small table and a big one. The hash method seems to me the right solution. i develop this program, but the result whith hash method is different whith sql In the case where I have to join with data many to many, how can I adapt this solution? thank you data a ;
input key adata adata2 ;
cards ;
1 1 2
1 1 3
2 2 5
3 3 4
4 4 6
5 5 5
6 6 6
7 7 5
;
run ;
data b ;
input key bdata ;
cards ;
1 11
1 12
3 31
4 4
6 61
6 62
6 63
7 7
;
run ;
proc sql;
create table aa as select a.key,adata,bdata,adata2
from a left join b on a.key=b.key;
quit;
data c;
if _n_ = 1 then do ;
if 0 then set a ;
dcl hash b (dataset: "a", multidata: "y",ordered:'y') ;
b.definekey ("key") ;
b.definedata (all:'y') ;
b.definedone () ;
end;
do until(eof);
set b end=eof;
if b.find()=0 then output;
else do;
call missing(adata ,bdata, adata2);
output;
end;
end;
stop;
run;
... View more