topic Re: Inner join with keep variables from only one table in HASH in SAS Programming

Inner join with keep variables from only one table in HASH

Myurathan — Fri, 24 Jul 2020 15:26:50 GMT

Hi Experts,

I have two tables, Table DCS (2 billion rows) and table Wiki (1 million rows). I would like to translate the following SQL code into SAS Hash.

Proc SQL;

create table want as
Select
 a.*
from DCS as a
inner join Wiki as b
on a.sourcekey = b.S_Key and a.sourcesystemid = b.s_ID;
quit;

Sample data:

DCS table

Snapshot_date	Sourcekey	Sourcesystemid	Amount	Riskbucket
01/01/2000	958-985	5	5698	9
02/01/2000	958-985	5	3256	0
03/01/2000	958-985	5	3254	5
04/01/2000	958-985	5	8523	6
01/01/2000	958-986	7	5698	9
02/01/2000	958-986	7	3256	0
03/01/2000	958-986	7	3254	5
04/01/2000	958-986	7	8523	6
01/01/2000	958-989	2	5698	9
02/01/2000	958-989	2	3256	0
03/01/2000	958-989	2	3254	5
04/01/2000	958-989	2	8523	6
01/01/2000	958-989	6	5698	9
02/01/2000	958-989	6	3256	0
03/01/2000	958-989	6	3254	5
04/01/2000	958-989	2	8523	6

Wiki table

Sourcekey	Sourcesystemid
958-985	5
958-986	7
958-999	5
958-989	2

Results table

Snapshot_date	Sourcekey	Sourcesystemid	Amount	Riskbucket
01/01/2000	958-985	5	5698	9
02/01/2000	958-985	5	3256	0
03/01/2000	958-985	5	3254	5
04/01/2000	958-985	5	8523	6
01/01/2000	958-986	7	5698	9
02/01/2000	958-986	7	3256	0
03/01/2000	958-986	7	3254	5
04/01/2000	958-986	7	8523	6
01/01/2000	958-989	2	5698	9
02/01/2000	958-989	2	3256	0
03/01/2000	958-989	2	3254	5
04/01/2000	958-989	2	8523	6

Since these tables are large, I think HASH technique will help me to get the results quickly.

Thanks in advance.

Regards,

Myu

Re: Inner join with keep variables from only one table in HASH

RichardDeVen — Fri, 24 Jul 2020 16:16:40 GMT

Hash the 1M keys from Wiki
Implicitly loop DCS with SET
Output only on key found (.check() method)

Example:

Data:

data DCS;
input Snapshot_date mmddyy10.	Sourcekey	$ Sourcesystemid	Amount	Riskbucket;
format Snapshot_date mmddyy10.;
datalines;
01/01/2000	958-985	5	5698	9
02/01/2000	958-985	5	3256	0
03/01/2000	958-985	5	3254	5
04/01/2000	958-985	5	8523	6
01/01/2000	958-986	7	5698	9
02/01/2000	958-986	7	3256	0
03/01/2000	958-986	7	3254	5
04/01/2000	958-986	7	8523	6
01/01/2000	958-989	2	5698	9
02/01/2000	958-989	2	3256	0
03/01/2000	958-989	2	3254	5
04/01/2000	958-989	2	8523	6
01/01/2000	958-989	6	5698	9
02/01/2000	958-989	6	3256	0
03/01/2000	958-989	6	3254	5
04/01/2000	958-989	2	8523	6
;

data Wiki;
input Sourcekey $	Sourcesystemid;
datalines;
958-985	5
958-986	7
958-999	5
958-989	2
;

Code:

data want;
  if _n_ = 1 then do;
    declare hash wiki_lookup(dataset:'wiki');
    wiki_lookup.defineKey('Sourcekey', 'Sourcesystemid');
    wiki_lookup.defineDone();
  end;

  set DCS;

  if wiki_lookup.check() = 0;
run;

Note, your sample data has a last row

04/01/2000	958-989	2	8523	6

that is a duplicate of an earlier row

Re: Inner join with keep variables from only one table in HASH

PGStats — Fri, 24 Jul 2020 16:32:16 GMT

@RichardDeVen proposed a very good solution. You may also want to add the argument HASHEXP:9 or HASHEXP:10 to the DECLARE statement to increase the hash table size and make it more efficient when dealing with a million entries.

Re: Inner join with keep variables from only one table in HASH

Myurathan — Sat, 25 Jul 2020 09:30:16 GMT

@RichardADeVenezia Thank you so much for your help.
followup question: if I want to do left join and wanna create a variable (wiki_Flag) that indicate that the contract is available in wiki table. How can I achieve this using HASH?
Thanks in advance.

Re: Inner join with keep variables from only one table in HASH

RichardDeVen — Sat, 25 Jul 2020 11:08:25 GMT

The hash method check returns 0 when a hash key corresponding to the current PDV variables exists.

An edit of the source code line from

  if wiki_lookup.check() = 0;

  wiki_flag = (wiki_lookup.check() = 0);

will change the program flow from a subsetting-if to a flag variable assignment

Re: Inner join with keep variables from only one table in HASH

ChrisNZ — Sat, 25 Jul 2020 11:14:04 GMT

1. How can I achieve this using HASH?

Change

  if wiki_lookup.check() = 0;

  WIKI_FLAG = ( wiki_lookup.check() = 0 );

2. Scanning every row of a large table takes time. If the large table is indexed, retrieving 1/2000th of the table using indexes might be faster.