turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- SQL JOIN

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2010 02:35 PM

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

1 1

1 2

1 3

1 5

1 8

2 8

2 5

2 3

2 1

2 2

Table 2

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

For e.g., for the first record (

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DB_ECON

10-08-2010 09:19 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

10-09-2010 03:58 AM

:-) 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DB_ECON

10-27-2010 09:33 AM

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