topic Re: HASH join? in SAS Procedures

HASH join?

sassharp — Fri, 11 May 2012 15:22:26 GMT

In hash join for testing purposes I do need to use for only one record out of dataset. I mean how the look up process or join would be by using one ID. For eaxmplae in proc sql left join

proc sql;

create table test as

select *

from table1

left join

table2

where table1.id=4 and table1.id=table2.id;

run;

Re: HASH join?

Haikuo — Fri, 11 May 2012 15:29:25 GMT

Hi, if I understand you correctly:

blah blah blah

yourhash.definekey('id');

blah blah blah

rc=yourhash.find(key:4);

blah blah blah

Regards,

Haikuo

Re: HASH join?

sassharp — Fri, 11 May 2012 17:08:21 GMT

@HaiKuo

would you change this code for any one EMPID?

you can find the data sets in your sasuser library.

data match_on_movie_titles(drop=rc);

if 0 then set sasuser.flightschedule sasuser.flightattendants; /* load variable properties into hash tables */

if _n_ = 1 then do;

declare Hash MatchTitles (dataset:"sasuser.flightattendants"); /* declare the name MatchTitles

for hash */

MatchTitles.DefineKey ("EmpID"); /* identify variable to use as key */

MatchTitles.DefineData (

"EmpID","JobCode","LastName","FirstName"); /* identify columns of data */

MatchTitles.DefineDone (); /* complete hash table definition */

end;

set sasuser.flightschedule;

if MatchTitles.find(key:EMPID) = 0 then output; /* lookup TITLE in MOVIES table

using MatchTitles hash */

run;

Re: HASH join?

sassharp — Fri, 11 May 2012 18:58:34 GMT

data match_on_movie_titles(drop=rc);

if 0 then set sasuser.flightschedule sasuser.flightattendants; /* load variable properties into hash tables */

if _n_ = 1 then do;

declare Hash MatchTitles (dataset: "sasuser.flightattendants"); /* declare the name MatchTitles

for hash */

MatchTitles.DefineKey ("EmpID"); /* identify variable to use as key */

MatchTitles.DefineData (

"EmpID","JobCode","LastName","FirstName"); /* identify columns of data */

MatchTitles.DefineDone (); /* complete hash table definition */

end;

set sasuser.flightschedule(where=(EmpID='1113'));

if MatchTitles.find(key:EmpID)=0 then output; /* lookup TITLE in MOVIES table

using MatchTitles hash */

run;