How to find max value in each month

Reply
Occasional Learner
Posts: 1

How to find max value in each month

[ Edited ]

Original table:

IDDateCategoryValue
11/20/18d10
21/22/18f15
32/1/18e12
42/13/18d9
52/17/18f8
63/11/18e14
73/22/18e18
83/19/18f10

 

I need help finding the ID with the maximum value in each month. I want my table to look like this:

 

IDDateCategoryValue
21/22/18f15
32/1/18e12
73/22/18e18
    
PROC Star
Posts: 1,833

Re: How to find max value in each month

Posted in reply to saslearner321
data have;
input ID	Date :mmddyy8.	Category $	Value  ;
format date mmddyy10.;

datalines;
1	1/20/18	d	10
2	1/22/18	f	15
3	2/1/18	e	12
4	2/13/18	d	9
5	2/17/18	f	8
6	3/11/18	e	14
7	3/22/18	e	18
8	3/19/18	f	10
;

proc sql;
create table want as
select * 
from have
group by month(date) 
having value=max(value);
quit;
Super User
Posts: 13,583

Re: How to find max value in each month

Posted in reply to saslearner321

First question. Are your dates actually SAS date values or character. I ask because most of the time SAS date displays would show 02/01/18 instead of 2/1/18.

Second does your data cross calendar year? If so by Month then do you mean all January data or each year's January?

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Also a data step means that we do not need to ask if a value is character or numeric.

Ask a Question
Discussion stats
  • 2 replies
  • 162 views
  • 0 likes
  • 3 in conversation