BookmarkSubscribeRSS Feed
jpm2478
Calcite | Level 5

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
4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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?

jpm2478
Calcite | Level 5
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.
Patrick
Opal | Level 21

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

FreelanceReinh
Jade | Level 19

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1009 views
  • 0 likes
  • 4 in conversation