topic Re: Left outer join using HASH - only rows from the left table in New SAS User

Left outer join using HASH - only rows from the left table

Matos — Wed, 20 May 2020 06:55:25 GMT

Dear all,

I was trying to execute a proc sql left join in order to display only the rows from the left table that do not match with the right table (conceptually: select * from TableA A Left Join TableB B on A.key=B.Key where B.key Is Null). The problem is my table A is huge, and I am using a key with 4 columns, and as such the proc sql takes a lot of time. If possible, I would like to get the same results using HASH since I understood it can have a very good performance for this kind of things. Any help appreciated.

Example with TableA and TableB (key Col1, Col2, Col3, Col4):

TableA
Col1	Col2	Col3	Col4	Col5
A	1	6	1	2
A	2	4	1	2
B	1	5	1	2
A	2	4	1	2
C	1	5	1	2
A	1	5	1	2
D	6	1	1	2

TableB
Col1	Col2	Col3	Col4
A	1	5	1
B	1	5	1
D	6	1	1
E	7	1	1

Desired result
Col1	Col2	Col3	Col4	Col5
A	1	6	1	2
A	2	4	1	2
A	2	4	1	2
C	1	5	1	2

Re: Left outer join using HASH - only rows from the left table

PeterClemmensen — Wed, 20 May 2020 07:02:52 GMT

Try this

data TableA;
input Col1 $ Col2 Col3 Col4 Col5;
datalines;
A 1 6 1 2
A 2 4 1 2
B 1 5 1 2
A 2 4 1 2
C 1 5 1 2
A 1 5 1 2
D 6 1 1 2
;

data TableB;
input Col1 $ Col2 Col3 Col4;
datalines;
A 1 5 1
B 1 5 1
D 6 1 1
E 7 1 1
;

data want;
    if _N_ = 1 then do;
        declare hash h (dataset : "TableB");
        h.definekey ("Col1", "Col2", "Col3", "Col4");
        h.definedone ();
    end;

    set TableA;

    if h.check() ne 0;
run;

Re: Left outer join using HASH - only rows from the left table

PeterClemmensen — Wed, 20 May 2020 07:06:07 GMT

Also, how huge is 'huge'? And what about the size of TableB?

Re: Left outer join using HASH - only rows from the left table

Matos — Wed, 20 May 2020 07:15:20 GMT

Table A has around 4 million records and table B around 2 million records. I wanted to exclude B from A if B exists in A.

Re: Left outer join using HASH - only rows from the left table

PeterClemmensen — Wed, 20 May 2020 07:19:22 GMT

Did you try my code?

Re: Left outer join using HASH - only rows from the left table

Matos — Wed, 20 May 2020 07:20:10 GMT

Apparently it works and seems incredible fast. Thanks a lot.

Re: Left outer join using HASH - only rows from the left table

PeterClemmensen — Wed, 20 May 2020 07:22:20 GMT

Anytime. If your sample data is representative, then you can simplify a bit by

data want;
    if _N_ = 1 then do;
        declare hash h (dataset : "TableB");
        h.definekey (all : "Y");
        h.definedone ();
    end;

    set TableA;

    if h.check() ne 0;
run;

Re: Left outer join using HASH - only rows from the left table

Matos — Wed, 20 May 2020 07:23:58 GMT

Great! It seems to work. I will just perform some checks.