topic Re: HASH Vlookup in SAS Data Management

HASH Vlookup

jpm2478 — Mon, 30 Jul 2018 19:36:54 GMT

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.

Key	Loc	Pt_key	Pt_Loc
101	ORD	102	STL
105	LIC	505	MCI
107	MTI	209	QUI
102	STL	103	KOI
200	FBI	108	PIY
201	INV	101	ORD
202	ROC	603	LOB

Re: HASH Vlookup

PeterClemmensen — Mon, 30 Jul 2018 19:39:22 GMT

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?

Re: HASH Vlookup

jpm2478 — Mon, 30 Jul 2018 21:41:36 GMT

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.

Re: HASH Vlookup

Patrick — Tue, 31 Jul 2018 05:31:53 GMT

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

Re: HASH Vlookup

FreelanceReinh — Tue, 31 Jul 2018 08:17:59 GMT

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;