SQL JOIN

[DB_ECON]

Suppose I have two tables.

Table 1
week term

1 1
1 2
1 3
1 5
1 8
2 8
2 5
2 3
2 1
2 2

Table 2
week term score
1 1 5
1 2 9
1 3 7
1 4 6
1 7 3
2 2 4
2 4 7
2 5 3
2 1 2
2 9 6

I want to attach score from table 2 to table 1 in a particular way. For each week and each term in table 1, grab the variable term in table 2 (which is not more than + 1 or - 1 units from term in table 1) from that week which has the highest score .

For e.g., for the first record (week 1 and term 1 in table 1), there are two candidate scores in table 2 : Term 1 week 1, which has a score of 5 and term 2 week 1, which has a score of 9. I want to select the max of 5 and 9 which is 9. So the first record in table 1 is given a score of 9.

Can anyone help me create this new table in SQL. I already know how to do this using transpose and a bunch of data steps. However I have too many groups and values which will make data step processing cumbersome.

Thanks in advance. Message was edited by: DB_ECON

[Patrick]

Try this:

proc sql;
select distinct
l.week
, l.term
, r.score

from table1 L left join table2 R
on l.week=r.week
and r.term between l.term-1 and l.term+1

group by l.week, l.term
having r.score=max(r.score)
;
quit;

[Ksharp]

🙂 Long time no see.
I have not enter this forum for a long time (a couple of weeks) .
Cartesian Product would be useful, but To the large dataset ,It will waste lots of time.

[pre]
data table1;
input week term;
datalines;
1 1
1 2
1 3
1 5
1 8
2 8
2 5
2 3
2 1
2 2
;
data table2;
input week term score;
datalines;
1 1 5
1 2 9
1 3 7
1 4 6
1 7 3
2 2 4
2 4 7
2 5 3
2 1 2
2 9 6
;

proc sql feedback;
select table1.week,table1.term,max(table2.score) as score
from table1,table2
where table2.week eq table1.week and table2.term between (table1.term - 1) and (table1.term + 1)
group by table1.week,table1.term;
quit;

[/pre]


or use left join just as patrick mentioned which can save your resource.

[pre]
proc sql feedback;
select table1.week,table1.term,max(table2.score) as score
from table1 left join table2
on table1.week eq table2.week
where table2.term between (table1.term - 1) and (table1.term + 1)
group by table1.week,table1.term;
quit;
[/pre]





I have to work now.Hope my code is right. 😉
and I test it ,it does work.
I really hope to spend enough time on this forum. But......

Ksharp Message was edited by: Ksharp

[DB_ECON]

Sorry for the late reply Patrick and Ksharp. My apologies. Both codes worked brilliantly.
Thanks a lot for your help.

DB