proc sql

[jpeevy]

I have a data set of items, year, and levels, similar to this. Each product has at least one entry each year, but sometimes might also have two per year. If that have two, then they also have different levels:

inventory

ID	Year	Product	level
1	1999	apple	1
2	1999	jack	3
3	1999	apple	2
4	2000	phone	4
5	2000	desk	1
6	2000	jack	2

Using Proc SQl, I am trying to create a new data set composed of entries that for each year list all the products with their highest level. . I am working with this code below, but have not been able to get the results I want. so it would look like:

ID	Year	Product	Level
1	1999	apple	2
2	1999	jack	3
3	2000	phone	4
4	2000	desk	1
5	2000	jack	2

Code:

proc sql;

create table prod_1 as

select 

Id,

Year,

product,

level

from inventory

where inventory.level = (select max(level)

.... here I tried different where clauses, but nothing is working...

quit

Any help would be much appreciated.

Thanks everyone!

[stat_sas]

proc sql;

select * from have

group by year,product

having level=max(level);

quit;

[jpeevy]

Thank you. I was not familiar with the 'having' clause and am reading about it now.

Thanks again.