Help using Base SAS procedures

HASH join?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

HASH join?

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;



Accepted Solutions
Solution
‎05-11-2012 02:58 PM
Frequent Contributor
Posts: 110

Re: HASH join?

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: HASH join?

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

Frequent Contributor
Posts: 110

Re: HASH join?

@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;

Solution
‎05-11-2012 02:58 PM
Frequent Contributor
Posts: 110

Re: HASH join?

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 331 views
  • 0 likes
  • 2 in conversation