SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

HASH Vlookup

Reply
Occasional Contributor
Posts: 16

HASH Vlookup

Hi,

 

I'm using Hash Tables. My question is how do I do a vlookup type in HASH on the same Dataset. Below is the set and the last column is the lookup on Key.

 

I want to do it using Hash as I have over 3 million rows.

 

KeyLocPt_keyPt_Loc
101ORD102STL
105LIC505MCI
107MTI209QUI
102STL103KOI
200FBI108PIY
201INV101ORD
202ROC603LOB
PROC Star
Posts: 1,400

Re: HASH Vlookup

I don't get it. Is this your actual data? Why do you want to look something up that is already in your data?

 

Please be more specific and shows us your desired outcome?

Occasional Contributor
Posts: 16

Re: HASH Vlookup

It's an Inner Join.  The Pt_Loc is based upon the Pt_key The Pt_key & Key are location specific. It is exactly as if you do a vlookup in last column and reference column A & B.
Respected Advisor
Posts: 4,779

Re: HASH Vlookup


@jpm2478 wrote:
It's an Inner Join.  The Pt_Loc is based upon the Pt_key The Pt_key & Key are location specific. It is exactly as if you do a vlookup in last column and reference column A & B.

The data you've posted is the result you're after but doesn't show us the source tables. Can you please post these and then show us the desired result?

Trusted Advisor
Posts: 1,318

Re: HASH Vlookup

Hi @jpm2478,

 

So, you have a dataset like this?

data have;
input Key Loc $ Pt_key;
cards;
101 ORD 102
105 LIC 505
107 MTI 209
102 STL 103
200 FBI 108
201 INV 101
202 ROC 603
;

And you want to add column Pt_Loc containing the Loc value from dataset HAVE that can be found by looking up Pt_key in column Key (searching top-down until the first match, assigning a missing value if no matching Key is found)?

 

This could be implemented as follows:

data want;
if _n_=1 then do;
  dcl hash h(dataset:'have(rename=(loc=_loc))');
  h.definekey('key');
  h.definedata('_loc');
  h.definedone();
  if 0 then set have(keep=loc rename=(loc=_loc));
end;
set have;
if h.find(key: pt_key)=0 then Pt_Loc=_loc;
drop _loc;
run;

 

Ask a Question
Discussion stats
  • 4 replies
  • 106 views
  • 0 likes
  • 4 in conversation