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?
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
Sorry,
The you corrected it produced the wrong results. Table3 has ALL of Table1. It's like the where was ignored.
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: