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 |
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;
Thank your generous help. It helps a lot
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.