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!
proc sql;
select * from have
group by year,product
having level=max(level);
quit;
Thank you. I was not familiar with the 'having' clause and am reading about it now.
Thanks again.
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.