I have the following two datasets:
LOOKUP DATASET:
Country_Code | Country_Name |
BR | Brazil |
GB | United Kingdom |
KW | Kuwait |
SA | Saudi Arabia |
DE | Germany |
ES | Spain |
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
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;
Hi,
Please use the search box on the main page, and type in Hash. There are over 30 very good examples which come up.
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;
Thanks a lot Xia, you are a savior!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.