I am new to hash tables, I need to merge two tables where I used join which takes more time to run. So I decided to use the below code.
My code works without error but the output value is same for all the records.
Please advise how to fix it.
Layout of IFILE is TRANID, SEND_CNTY and LOOKUP file is COUNTRY NAME, COUNTRY CODE
Input FILE
TRAN_ID SEND_CNTY
T123 EH
T456 ZM
T678 US
T444 ZW
Lookup file
COUNTRY CNTY_CODE2
Western Sahara |
EH |
Yemen |
YE |
Zambia |
ZM |
Zimbabwe |
ZW |
In output I am getting all as Zimbabwe
TRAN_ID SEND_CNTY COUNTRY CNTY_CODE2
T123 EH Zimbabwe ZW
T456 ZM Zimbabwe ZW
T678 US Zimbabwe ZW
T444 ZW Zimbabwe ZW
DATA OFILE (DROP=RC);
RENAME SEND_CNTY = CNTY_CODE2;
LENGTH CNTY_CODE2 $2.;
DECLARE HASH CNTY ();
RC=CNTY.DEFINEKEY('CNTY_CODE2');
RC=CNTY.DEFINEDATA('COUNTRY');
RC=CNTY.DEFINEDONE();
DO UNTIL (EOF1);
SET CNTY_LOOKUP END=EOF1;
RC=CNTY.ADD();
END;
DO UNTIL (EOF2);
SET IFILE END=EOF2;
CALL MISSING(COUNTRY);
RC=CNTY.FIND();
OUTPUT;
END;
STOP;
RUN;
For this type of lookup you can use a format.
You don't even have to recreate the dataset, you can apply the format via proc datasets.
You may also be able to apply the format when you use it later on instead of on the big table.
Just a suggestion. Good luck with your hash solution.
A paper that compares performance of methods:
https://support.sas.com/resources/papers/proceedings09/037-2009.pdf
For this type of lookup you can use a format.
You don't even have to recreate the dataset, you can apply the format via proc datasets.
You may also be able to apply the format when you use it later on instead of on the big table.
Just a suggestion. Good luck with your hash solution.
A paper that compares performance of methods:
https://support.sas.com/resources/papers/proceedings09/037-2009.pdf
The key variable have two different name in two table.
data IFILE ;
input TRAN_ID $ SEND_CNTY $;
cards;
T123 EH
T456 ZM
T678 US
T444 ZW
;
run;
data CNTY_LOOKUP ;
input COUNTRY & $40. CNTY_CODE2 $;
cards;
Western Sahara EH
Yemen YE
Zambia ZM
Zimbabwe ZW
;
run;
data want;
if _n_=1 then do;
if 0 then set CNTY_LOOKUP;
declare hash h(dataset:'CNTY_LOOKUP');
h.definekey('CNTY_CODE2');
h.definedata('COUNTRY');
h.definedone();
end;
call missing(of _all_);
set IFILE;
rc=h.find(key:SEND_CNTY);
drop rc;
run;
Thanks Reeza and Ksharp.
I used PROC FORMAT as suggested by Reeza and works fine for me.
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.