BookmarkSubscribeRSS Feed
jpeevy
Calcite | Level 5

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

IDYearProductlevel
11999apple1
21999jack3
31999

apple

2
42000phone4
52000

desk

1
62000jack2

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:

IDYearProductLevel
11999apple2
21999jack3
32000phone4
42000desk1
52000jack2

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!

2 REPLIES 2
stat_sas
Ammonite | Level 13

proc sql;

select * from have

group by year,product

having level=max(level);

quit;

jpeevy
Calcite | Level 5

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

Thanks again.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 926 views
  • 1 like
  • 2 in conversation