topic Re: Left join with hash: many to many in SAS Programming

Left join with hash: many to many

mansour_ib_sas — Thu, 20 Sep 2018 14:09:55 GMT

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;

Re: Left join with hash: many to many

novinosrin — Thu, 20 Sep 2018 14:28:12 GMT

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 want_hash;
 if 0 then set a b ;
 dcl hash b (dataset: "b", multidata: "y",ordered:'y') ;
 b.definekey ("key") ;
 b.definedata (all:'y') ;
 b.definedone () ;
  do until(eof);
   set a end=eof;
   rc=b.find();
   if rc ne 0 then do;
	call missing(bdata);
	output;
	end;
   else 
    do  while(rc eq 0);
     output;
	 rc=b.find_next();
    end;
 end;
stop;
drop rc;
run;

Re: Left join with hash: many to many

mansour_ib_sas — Thu, 20 Sep 2018 14:34:26 GMT

Thank you

Re: Left join with hash: many to many

novinosrin — Thu, 20 Sep 2018 14:35:19 GMT

Some Notes:

1. No need for if _n_=1 when going for a full pass of dataset while _n_=1, since there is gonna be only one hash instance at run time

2. do until pass akin to dow is apparently not any different in performance if you used if _n_=1 ,creating a hash instance and a traditional data-step as portrayed in sas docs.

3. If you are using PD hash guru's style of coding, great but you have to be very careful knowing how instances value vary and differ.

Well after all, it's all about diligence

Re: Left join with hash: many to many

mansour_ib_sas — Thu, 20 Sep 2018 15:21:15 GMT

We can add a filtre on the date column in the big table?

Re: Left join with hash: many to many

novinosrin — Thu, 20 Sep 2018 15:22:27 GMT

I don't see any date values in your samples. I assume you can

Re: Left join with hash: many to many

mansour_ib_sas — Fri, 21 Sep 2018 14:01:29 GMT

Hello,

Why the 8 and 9 did not come in result whith Hash method?

thank you

data  a ;
input key adata adata2 ;
cards ;
1 1 2
1 1 3
2 2 5
8 7 5
9 5 4
;
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 yes as select a.*,b.bdata
from a left join b on a.key=b.key;
quit;


data want_hash;
 if 0 then set a ;
 dcl hash b (dataset: "a", multidata: "y",ordered:'y') ;
 b.definekey ("key") ;
 b.definedata (all:'y') ;
 b.definedone () ;
  do until(eof);
   set b end=eof;
   rc=b.find();
   if rc ne 0 then do;
	call missing(bdata);
	output;
	end;
   else 
    do  while(rc eq 0);
     output;
	 rc=b.find_next();
    end;
 end;
stop;

run;

Re: Left join with hash: many to many

novinosrin — Fri, 21 Sep 2018 14:20:36 GMT

Your left table is a , but in your code

  do until(eof);
   set b end=eof;

so change the above to

  do until(eof);
   set a end=eof;

Re: Left join with hash: many to many

mansour_ib_sas — Fri, 21 Sep 2018 14:30:42 GMT

Yes, the table A is the left table.

It's the small table that is loaded in memory through declare the object hash

In the statement set; the big table is B.

Re: Left join with hash: many to many

novinosrin — Fri, 21 Sep 2018 14:40:23 GMT

to match the sql results, the change i suggested works

Re: Left join with hash: many to many

mansour_ib_sas — Fri, 21 Sep 2018 14:44:01 GMT

The table B have 6 millions obseravation. loaded this in memory ?

Re: Left join with hash: many to many

novinosrin — Fri, 21 Sep 2018 14:46:04 GMT

Ok, please clarify and elaborate your requirement with your HAVE samples and WANT sample clearly. Let us work on the solution.

6 million is not big in my opinion. Use hashexp:20 just in case