Solved
Contributor
Posts: 29

# Hash Object?

Hello,

I need some guidance. I am stuck in this problem and was wondering if using hash objets for table lookup is the right approach.

Situation:

I have a vertical table which only consists of a key code and an assigned factor:

 KeyCode Factor 001_1 0.25 001_2 0.3 001_3 0.55 001_4 0.85

Then, I have another table with a horizontal layout, with key codes in columns and a static amount:

 Amount KeyCode1 KeyCode2 KeyCode3 KeyCode4 10 001_1 001_1 001_1 001_1 12 001_1 001_2 001_2 001_2 15 001_1 001_2 001_3 001_4

I want to create a third data set by multiplying the amount in the second table to the corresponding factor from the first table:

 Factor*Amount (Using keycode for lookup) 2.5 2.5 2.5 2.5 3 3.6 3.6 3.6 3.75 4.5 8.25 12.75

Accepted Solutions
Solution
‎08-09-2013 08:28 PM
Super Contributor
Posts: 308

## Re: Hash Object?

How about create a format and apply it to the amounts? Something like this . . .

/* create table of codes-factors as per example provided */

data keycode ;
input keycode \$ factor ;
datalines;
001_1 0.25
001_2 0.3
001_3 0.55
001_4 0.85
;
;;;;;

/* reformat the data to read it into proc format */

data keycdfmt ;
label fmtname='' start='' label='' ; /* label statement used to get the variables in a preferred order for proc format */
set keycode (rename=(keycode=start factor=label));
fmtname = '\$keycode';
run ;

/* create the format */

proc format cntlin=keycdfmt ;
run;

/* create table of amounts as per example provided */

data amounts ;
input amount 2. keycode1 \$ keycode2 \$ keycode3 \$ keycode4 \$ ;
datalines;
10 001_1 001_1 001_1 001_1
12 001_1 001_2 001_2 001_2
15 001_1 001_2 001_3 001_4
;
;;;;

/* calculate new amounts using proc format */

data newamounts ;
set amounts ;
keep newamt1-newamt4;
array keycode[4]; /* already defined as character */
array keycdnm[4] ;
array newamt [4] ;
do i = 1 to 4 ;
keycdnm= put(keycode, \$keycode.);
newamt = keycdnm*amount ;
end;
run ;

All Replies
Solution
‎08-09-2013 08:28 PM
Super Contributor
Posts: 308

## Re: Hash Object?

How about create a format and apply it to the amounts? Something like this . . .

/* create table of codes-factors as per example provided */

data keycode ;
input keycode \$ factor ;
datalines;
001_1 0.25
001_2 0.3
001_3 0.55
001_4 0.85
;
;;;;;

/* reformat the data to read it into proc format */

data keycdfmt ;
label fmtname='' start='' label='' ; /* label statement used to get the variables in a preferred order for proc format */
set keycode (rename=(keycode=start factor=label));
fmtname = '\$keycode';
run ;

/* create the format */

proc format cntlin=keycdfmt ;
run;

/* create table of amounts as per example provided */

data amounts ;
input amount 2. keycode1 \$ keycode2 \$ keycode3 \$ keycode4 \$ ;
datalines;
10 001_1 001_1 001_1 001_1
12 001_1 001_2 001_2 001_2
15 001_1 001_2 001_3 001_4
;
;;;;

/* calculate new amounts using proc format */

data newamounts ;
set amounts ;
keep newamt1-newamt4;
array keycode[4]; /* already defined as character */
array keycdnm[4] ;
array newamt [4] ;
do i = 1 to 4 ;
keycdnm= put(keycode, \$keycode.);
newamt = keycdnm*amount ;
end;
run ;

Posts: 3,167

## Re: Hash Object?

Many times format is better than Hash() when doing look-up, in term of simplicity and efficiency. Here is a Hash() approach :

data _hash;

input keycode \$ factor;

cards;

001_1 0.25

001_2 0.3

001_3 0.55

001_4 0.85

;

data have;

input

Amount (KeyCode1 KeyCode2 KeyCode3 KeyCode4) (\$);

cards;

10 001_1 001_1 001_1 001_1

12 001_1 001_2 001_2 001_2

15 001_1 001_2 001_3 001_4

;

data want;

array amt amt1-amt4;

if _n_=1 then do;

if 0 then set _hash;

declare hash h(dataset:'_hash');

h.definekey('keycode');

h.definedata('factor');

h.definedone();

end;

set have;

array key keycode1-keycode4;

do over key;

amt=(h.find(key:key)=0)*factor*amount;

end;

keep amt:;

run;

Haikuo

🔒 This topic is solved and locked.