Inner Join Doesn't Produce Desired Result

[Terrell]

I have two tables which have been combined using an inner join.

Table1

Row     ID     Amount

1     555     567.23

2     555     782.10

3     555     56.00

4     444     3.00

5     444     10.10

Table2 is built using the following query:

proc sql;

create Table2 as

select max(Amount) as Amount

from Table1

group on ID

;quit;

proc sql;

create Table3 as

select a.ID, a.Amount

from Table1 as a, Table2 as b

where a.Amount = b.Amount

;quit;

Result in Table3 is ALL of the records in Table1 rather than the two matches.

Adding ID to Table2 and then adding ID to the where in Table3 produces the correct results.

Why wouldn't the first work since the amounts are unique?

[PGStats]

It works the way you expect once you fix the syntax

proc sql;

create table Table2 as

select max(Amount) as Amount

from Table1

group by ID

;

quit;

proc sql;

create table Table3 as

select a.ID, a.Amount

from Table1 as a, Table2 as b

where a.Amount = b.Amount;

quit;

PG

[Terrell]

Sorry,

The you corrected it produced the wrong results.  Table3 has ALL of Table1.  It's like the where was ignored.

[PGStats]

Here is my log:

1    data Table1;

2    input Row     ID     Amount;

3    datalines;

NOTE: The data set WORK.TABLE1 has 5 observations and 3 variables.

NOTE: DATA statement used (Total process time):

      real time           0.09 seconds

      cpu time            0.01 seconds

9    ;

10

11   proc sql;

12   create table Table2 as

13   select max(Amount) as Amount

14   from Table1

15   group by ID

16   ;

NOTE: Table WORK.TABLE2 created, with 2 rows and 1 columns.

17   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.14 seconds

      cpu time            0.07 seconds

18

19   proc sql;

20   create table Table3 as

21   select a.ID, a.Amount

22   from Table1 as a, Table2 as b

23   where a.Amount = b.Amount;

NOTE: Table WORK.TABLE3 created, with 2 rows and 2 columns.

24   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.03 seconds

      cpu time            0.03 seconds

PG

[Steelers_In_DC]

I'm not sure what you are looking for by your description but it doesn't sound like you are getting it, do you want to drop the highest value for each id?  If so this will work:

data have;

infile cards;

input Row     ID     Amount;

cards;

1     555     567.23

2     555     782.10

3     555     56.00

4     444     3.00

5     444     10.10

;

run;

proc sort data= have;by id amount;

data want;

set have;

by id amount;

if not last.id;

run;

[Steelers_In_DC]

Here's a way to do it in one step:

proc sql;

create table table2 as

select *

from table1

group by id

having max(amount) ne amount

order by row;