proc sql

Reply
Occasional Contributor
Posts: 7

proc sql

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!

Trusted Advisor
Posts: 1,203

Re: proc sql

proc sql;

select * from have

group by year,product

having level=max(level);

quit;

Occasional Contributor
Posts: 7

Re: proc sql

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

Thanks again.

Ask a Question
Discussion stats
  • 2 replies
  • 165 views
  • 1 like
  • 2 in conversation