topic Re: Hash join with multiple tables in SAS Programming

Hash join with multiple tables

umashankersaini — Thu, 13 Dec 2018 06:52:35 GMT

Hi Team,

Kindly let me know, how to write the hash join for multiple tables.

Joins could be inner join, Left join and full join.

DATA A;
input Name $1. Salary Age PIN;
Cards;
A 200 20 40
B 300 30 60
C 400 40 80
D 500 50 100
E 600 60 120
F 700 70 140
;
Run;

DATA B;
input Name $1. Salary Grade $2. BIN;
Cards;
A 200 AA 20
G 800 GG 80
H 900 HH 90
I 1000 II 100
F 700 FF 70
;
Run;

DATA C;
input Name $1. dept;
Cards;
A 111
B 222
C 333
Z 000
;
RUN;

Looking for your kind support as i am learning hash joins.

Regards,

Uma Shanker Saini

Re: Hash join with multiple tables

singhsahab — Thu, 13 Dec 2018 07:38:50 GMT

Hi Umashanker,

Here is the code for inner join.hope so this will help.

DATA A;
input Name $1. Salary Age PIN;
Cards;
A 200 20 40
B 300 30 60
C 400 40 80
D 500 50 100
E 600 60 120
F 700 70 140
;
Run;

DATA B;
input Name $1. Salary Grade $2. BIN;
Cards;
A 200 AA 20
G 800 GG 80
H 900 HH 90
I 1000 II 100
F 700 FF 70
;
Run;

DATA C;
input Name $1. dept;
Cards;
A 111
B 222
C 333
Z 000
;
RUN;

data want;
if 0 then set a b c;
declare hash h1(dataset:'C');
h1.definekey('name');
h1.defineDATA(all:'Y');
H1.DEFINEDONE();
declare hash h2(dataset:'b');
h2.definekey('name');
h2.DEFINEDATa(all:'Y');
h2.definedone();
DO UNTIL(LR);
set a END=LR;
if h1.find(key:name)=0 AND H2.FIND(KEY:NAME)=0 then output;
END;
STOP;
run;

Thanks,

🙂

Re: Hash join with multiple tables

PeterClemmensen — Thu, 13 Dec 2018 09:03:43 GMT

Can you show us the code for the inner, left and full joins you want to perform using hash objects?

That way, we know what to shoot for 🙂

Re: Hash join with multiple tables

umashankersaini — Thu, 13 Dec 2018 10:18:12 GMT

Hi,

I am looking for the output of following query :

Proc sql;
Create table ABC_SQL_INNER as
select A.name,A.Salary,B.Grade,B.Bin,C.Dept
from A
Inner join B
on A.Name=B.name
inner join C
on A.Name=C.name;
quit;

Proc sql;
Create table ABC_SQL_left as
select A.name,A.Salary,B.Grade,B.Bin,C.Dept
from A
left join B
on A.Name=B.name
left join C
on A.Name=C.name;
quit;

Proc sql;
Create table ABC_SQL_full as
select A.name,A.Salary,B.Grade,B.Bin,C.Dept
from A
full join B
on A.Name=B.name
full join C
on A.Name=C.name;
quit;

Re: Hash join with multiple tables

PeterClemmensen — Thu, 13 Dec 2018 11:58:41 GMT

You can do the inner and left joins like this

DATA A;
input Name $1. Salary Age PIN;
Cards;
A 200 20 40
B 300 30 60
C 400 40 80
D 500 50 100
E 600 60 120
F 700 70 140
;
Run;

DATA B;
input Name $1. Salary Grade $2. BIN;
Cards;
A 200 AA 20
G 800 GG 80
H 900 HH 90
I 1000 II 100
F 700 FF 70
;
Run;

DATA C;
input Name $1. dept;
Cards;
A 111
B 222
C 333
Z 000
;
RUN;

/* Inner join */
Proc sql; 
Create table ABC_SQL_INNER as 
select A.name,A.Salary,B.Grade,B.Bin,C.Dept 
from A
Inner join B
on A.Name=B.name
inner join C
on A.Name=C.name;
quit;

data ABC_HASH_INNER(drop=rc:);
   if 0 then set B C;
   if _N_ = 1 then do;
      declare hash h1(dataset:"B");
      h1.defineKey('Name');
      h1.defineData('Grade', 'Bin');
      h1.defineDone();
      declare hash h2(dataset:"C");
      h2.defineKey('Name');
      h2.defineData('Dept');
      h2.defineDone();
   end;
   
   set A(keep=name Salary);

   rc1=h1.find();
   rc2=h2.find();

   if rc1=0 & rc2=0;
run;

proc compare base=ABC_SQL_INNER compare=ABC_HASH_INNER;
run;

/* Left join */
Proc sql; 
Create table ABC_SQL_left as 
select A.name,A.Salary,B.Grade,B.Bin,C.Dept 
from A
left join B
on A.Name=B.name
left join C
on A.Name=C.name;
quit;

data ABC_HASH_LEFT(drop=rc:);
   if 0 then set B C;
   if _N_ = 1 then do;
      declare hash h1(dataset:"B");
      h1.defineKey('Name');
      h1.defineData('Grade', 'Bin');
      h1.defineDone();
      declare hash h2(dataset:"C");
      h2.defineKey('Name');
      h2.defineData('Dept');
      h2.defineDone();
   end;
   
   set A(keep=name Salary);

   rc1=h1.find();
   rc2=h2.find();

   if rc1 ne 0 then call missing(Grade, Bin);
   if rc2 ne 0 then call missing(Dept);
run;

proc compare base=ABC_SQL_LEFT compare=ABC_HASH_LEFT;
run;