BookmarkSubscribeRSS Feed
umesh1
Fluorite | Level 6

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
umesh1
Fluorite | Level 6

Thank your generous help. It helps a lot

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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