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.

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!

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.

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
  • 2 replies
  • 394 views
  • 1 like
  • 2 in conversation