topic Re: Left join with hash in SAS Programming

Left join with hash

iscgonzalez — Thu, 01 Feb 2018 23:08:00 GMT

Hello!

I'm kind new on hash usage and I was wondering if there is a way to perform a left join with hash without having to load the big table into hash instead the small one.

I have been reading multiple papers and articles about this but I haven't been able to came up with an idea of how this could be performed.

This is the code I have by now:

data a ;
input key adata ;
cards ;
1 1
2 2
3 3
4 4
5 5
6 6
7 7
;
run ;
data b ;
input key bdata ;
cards ;
1 11
1 12
3 31
4 4
6 61
6 62
6 63
7 7
;
run ;


data c ;
 if _n_ = 1 then do ;
 if 0 then set b ;
 dcl hash b (dataset: "b", multidata: "y") ;
 b.definekey ("key") ;
 b.definedata ("bdata") ;
 b.definedone () ;
 end ;
 set a ;
 iorc = b.find() ;
 if iorc ne 0 then call missing (bdata) ;
 output ;
 do while (b.find_next() = 0) ;
 output ;
 end ;
run ;

Both of the sample datasets are small here, but I was thinking in an scenario where big table is really big, so I can't load it into memory.

Any suggestion? Thanks guys!

Re: Left join with hash

novinosrin — Thu, 01 Feb 2018 23:18:32 GMT

which one in your example, would you consider big or small to simulate?

Re: Left join with hash

novinosrin — Fri, 02 Feb 2018 00:09:55 GMT

dataset swap using your examples:

data c1 ;
 if _n_ = 1 then do ;
 if 0 then set a ;
 dcl hash b (dataset: "a", multidata: "y",ordered:'y') ;
 b.definekey ("key") ;
 b.definedata ("key","adata") ;
 b.definedone () ;
dcl hiter iter('b');
 end ;
 set b end=last;
 by key;
if  b.find()= 0 then do;
output;
if last.key   then b.remove();
end;
if last then do;
  rc = iter.first();
do while (rc = 0);
	call missing(bdata);
       output;
	  rc = iter.next();
   end;
end;
drop rc;
run;

Re: Left join with hash

iscgonzalez — Fri, 02 Feb 2018 00:24:51 GMT

The a one is the small, it has no duplicates. It could be seen as a client's list and the other one transactions done by the users

Re: Left join with hash

novinosrin — Fri, 02 Feb 2018 00:26:29 GMT

Ok so my assumption was right, I hope you have seen my code. Thank you!

Re: Left join with hash

iscgonzalez — Fri, 02 Feb 2018 14:33:55 GMT

Thanks!! that is a very nice approach to the solution, I guess I have to have the b table sorted to use this solution. But I think I can tweak a little the code using the find_next function to avoid the need of ordering it.

Anyway, thank you for your awsome reply!

Re: Left join with hash

novinosrin — Fri, 02 Feb 2018 17:26:13 GMT

And @iscgonzalez Thank you for your very impressive attention to detail. I wish I could think out of the box like you. Anyway here is a version that doesn't require sort:

data a ;
input key adata ;
cards ;
1 1
2 2
3 3
4 4
5 5
6 6
7 7
;
run ;

/*modified b dataset to make it unordered*/
data b ;
input key bdata ;
cards ;
1 11
6 61
1 12
3 31
6 62
4 4
6 63
7 7
;
run ;

data c2 ;
 if _n_ = 1 then do ;
 if 0 then set a ;
 dcl hash b (dataset: "a") ;
 b.definekey ("key") ;
 b.definedata ("key","adata") ;
 b.definedone () ;
dcl hiter iter('b');
 end ;
 do _n_=1 by 1 until(last);
 set b end=last;
 array t(100) ; /*this is arbitrary subscript number*/
if  b.find()= 0 then do;
if key not in t then t(_n_)=key;
output;
end;
if last then do;
	do _n_=1 to dim(t);
	if b.check(key:t(_n_))=0 then  b.remove(key:t(_n_));
	end;
	rc = iter.first();
	do while (rc = 0);
	call missing(bdata);
 	output;
 	rc = iter.next();
 	end;
end;
end;
drop rc t:;
run;