topic Re: Hash object problem in SAS Procedures

Hash object problem

umesh1 — Mon, 08 Feb 2016 14:57:32 GMT

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

Re: Hash object problem

RW9 — Mon, 08 Feb 2016 15:43:01 GMT

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;

Re: Hash object problem

umesh1 — Mon, 08 Feb 2016 19:56:39 GMT

Thank your generous help. It helps a lot