DATA Step, Macro, Functions and more

Table Lookup Using Hash Tables

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Table Lookup Using Hash Tables

I have the following two datasets:

LOOKUP DATASET:

Country_CodeCountry_Name
BRBrazil
GBUnited Kingdom
KWKuwait
SASaudi Arabia
DEGermany
ESSpain

Main Dataset:

Country
BR
BR
GB
KW
SA
DE
ES
ES
NO
US
US

I need to create a new variable in the main dataset based on the values found in the lookup dataset. The result should look as follows:

Country     Country_Name_Full

BR               Brazil

BR               Brazil

DE               Germany

............

I am trying to use hash tables for this. Any help is appreciated. Thanks


Accepted Solutions
Solution
‎07-21-2015 08:02 AM
Super User
Posts: 10,041

Re: Table Lookup Using Hash Tables

Posted in reply to sasmaverick

Code: Program.sas

data query;
infile cards truncover expandtabs;
input Country_Code $ Country_Name & $20.;
cards;
BR Brazil
GB United Kingdom
KW Kuwait
SA Saudi Arabia
DE Germany
ES Spain
;
run;
data Main;
input  Country $;
cards;
BR
BR
GB
KW
SA
DE
ES
ES
NO
US
US
;
run;
data want;
if _n_ eq 1 then do;
  if 0 then set query;
  declare hash ha(dataset:'query');
  ha.definekey('Country_Code');
  ha.definedata('Country_Name');
  ha.definedone();
end;
call missing(of _all_);
set Main;
rc=ha.find(key:Country );
drop rc Country_Code ;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: Table Lookup Using Hash Tables

Posted in reply to sasmaverick

Hi,

Please use the search box on the main page, and type in Hash.  There are over 30 very good examples which come up.

Solution
‎07-21-2015 08:02 AM
Super User
Posts: 10,041

Re: Table Lookup Using Hash Tables

Posted in reply to sasmaverick

Code: Program.sas

data query;
infile cards truncover expandtabs;
input Country_Code $ Country_Name & $20.;
cards;
BR Brazil
GB United Kingdom
KW Kuwait
SA Saudi Arabia
DE Germany
ES Spain
;
run;
data Main;
input  Country $;
cards;
BR
BR
GB
KW
SA
DE
ES
ES
NO
US
US
;
run;
data want;
if _n_ eq 1 then do;
  if 0 then set query;
  declare hash ha(dataset:'query');
  ha.definekey('Country_Code');
  ha.definedata('Country_Name');
  ha.definedone();
end;
call missing(of _all_);
set Main;
rc=ha.find(key:Country );
drop rc Country_Code ;
run;
Contributor
Posts: 64

Re: Table Lookup Using Hash Tables

Thanks a lot Xia, you are a savior!

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 254 views
  • 0 likes
  • 3 in conversation