topic Re: Inner Join Doesn't Produce Desired Result in SAS Procedures

Inner Join Doesn't Produce Desired Result

Terrell — Fri, 17 Jul 2015 15:06:35 GMT

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?

Re: Inner Join Doesn't Produce Desired Result

PGStats — Fri, 17 Jul 2015 15:20:37 GMT

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;

Re: Inner Join Doesn't Produce Desired Result

Terrell — Fri, 17 Jul 2015 15:23:55 GMT

Sorry,

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

Re: Inner Join Doesn't Produce Desired Result

Steelers_In_DC — Fri, 17 Jul 2015 15:39:05 GMT

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;

Re: Inner Join Doesn't Produce Desired Result

PGStats — Fri, 17 Jul 2015 15:42:50 GMT

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 ;

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

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

Re: Inner Join Doesn't Produce Desired Result

Steelers_In_DC — Fri, 17 Jul 2015 15:48:33 GMT

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;