BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angorwat
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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

 

 

Ksharp
Super User

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;
angorwat
Calcite | Level 5

Thanks Reeza and Ksharp. 

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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