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: 9,656

Re: Table Lookup Using Hash Tables

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,392

Re: Table Lookup Using Hash Tables

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: 9,656

Re: Table Lookup Using Hash Tables

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.

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

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