Help using Base SAS procedures

Hash object problem

Reply
Occasional Contributor
Posts: 17

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

 

 

Super User
Super User
Posts: 7,976

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

Re: Hash object problem

Thank your generous help. It helps a lot

Ask a Question
Discussion stats
  • 2 replies
  • 254 views
  • 0 likes
  • 2 in conversation