topic Re: Hash table - Merge value duplicate in SAS Programming

Hash table - Merge value duplicate

angorwat — Fri, 13 May 2016 04:17:16 GMT

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;

Re: Hash table - Merge value duplicate

Reeza — Fri, 13 May 2016 04:58:04 GMT

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

Re: Hash table - Merge value duplicate

Ksharp — Fri, 13 May 2016 07:05:51 GMT

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;

Re: Hash table - Merge value duplicate

angorwat — Wed, 18 May 2016 04:24:31 GMT

Thanks Reeza and Ksharp.

I used PROC FORMAT as suggested by Reeza and works fine for me.