Fluorite | Level 6

## Hash object problem

Need help with Coding.

Going to go down Table 1 one record at a time;

Joining on ProdNo and Group;

So trying to match up NetVal in table two;

So for first value for table 1 is -32 is less than 0 (so the value you want on Table 1 is .005 and .009 from table 2)

Second value of table 1 is 45 / Record number three in table two so you get the value .003 and .9855.

Third value is 65 is between 45 and 99 in table number two so you get the value of .003 and .9855 (Between 45 and 99 so you use the 45 values from table 2).

At the end you will have the result in Table number 3 Below;

Table 1.

 Prod No Group NetVal A 1 -32 A 1 45 A 1 65 A 2 22 A 2 12 A 3 15 A 4 25 B 1 32 B 1 87 B 1 92 C 1 2 C 1 -13

Table 2.

 Prod No Group NetValRange Mvalue Tvalue A 1 0 0.005 0.009 A 1 22 0.004 0.6444 A 1 45 0.003 0.9855 A 1 99 0.002 0.7477 A 2 0 0.001 0.6544 A 2 53 0.0057 0.4675 A 2 77 0.2344 0.2457 A 4 -12 0.6564 0.953 A 4 99 0.9708 0.7657 B 1 5 0.2345 0.234 B 1 87 0.1275 0.7569 B 1 89 0.7855 0.629 C 1 0 0.4335 0.1324 C 1 32 0.1276 0.9079

Output we need is as follows;

 Prod No Group NetVal Mvalue Tvalue A 1 -32 0.005 0.009 A 1 45 0.003 0.985476 A 1 65 0.003 0.985476 A 2 22 0.001 0.65443 A 2 12 0.001 0.65443 A 3 15 0 0 A 4 25 0.65643 0.953 B 1 32 0.2345 0.234 B 1 87 0.1275 0.75687 B 1 92 0.78548 0.629 C 1 2 0.4335 0.1324 C 1 -13 0.4335 0.1324

2 REPLIES 2
Diamond | Level 26

## Re: Hash object problem

Well, its useful if you put test data in the form of a datastep.  I have made some code here quickly, it takes the netval from the max() of those before the netvalrange in the given row.  You can change to fit your exact criteria.

```data table1;
input ProdNo \$ Group NetVal;
datalines;
A 1 -32
A 1 45
A 1 65
;
run;

data table2;
input ProdNo \$ Group NetValRange Mvalue Tvalue;
datalines;
A 1 0 0.005 0.009
A 1 22 0.004 0.6444
;
run;

proc sql;
create table WANT as
select  T2.*,
(select max(NETVAL) from TABLE1 where PRODNO=T2.PRODNO and GROUP=T2.GROUP and NETVAL <= T2.NETVALRANGE) as NETVAL
from    TABLE2 T2;
quit;```
Fluorite | Level 6

## Re: Hash object problem

Thank your generous help. It helps a lot

Discussion stats
• 2 replies
• 731 views
• 0 likes
• 2 in conversation