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!
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.