BookmarkSubscribeRSS Feed
Terrell
Calcite | Level 5

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?

5 REPLIES 5
PGStats
Opal | Level 21

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

PG
Terrell
Calcite | Level 5

Sorry,

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

PGStats
Opal | Level 21

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

PG
Steelers_In_DC
Barite | Level 11

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
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1087 views
  • 0 likes
  • 3 in conversation