DATA Step, Macro, Functions and more

Hash table - Merge value duplicate

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Hash table - Merge value duplicate

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;


Accepted Solutions
Solution
‎05-18-2016 12:22 AM
Super User
Posts: 19,772

Re: Hash table - Merge value duplicate

[ Edited ]

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

 

 

View solution in original post


All Replies
Solution
‎05-18-2016 12:22 AM
Super User
Posts: 19,772

Re: Hash table - Merge value duplicate

[ Edited ]

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

 

 

Super User
Posts: 10,023

Re: Hash table - Merge value duplicate

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;
Occasional Contributor
Posts: 18

Re: Hash table - Merge value duplicate

Thanks Reeza and Ksharp. 

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

 

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 250 views
  • 1 like
  • 3 in conversation