- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 10-08-2010 02:35 PM
(1601 views)
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
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
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
🙂 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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry for the late reply Patrick and Ksharp. My apologies. Both codes worked brilliantly.
Thanks a lot for your help.
DB
Thanks a lot for your help.
DB