topic Re: How to find max value in each month in SAS Programming

How to find max value in each month

saslearner321 — Wed, 02 May 2018 03:19:50 GMT

Original table:

ID	Date	Category	Value
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

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

ID	Date	Category	Value
2	1/22/18	f	15
3	2/1/18	e	12
7	3/22/18	e	18

Re: How to find max value in each month

novinosrin — Wed, 02 May 2018 04:06:27 GMT

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;

Re: How to find max value in each month

ballardw — Wed, 02 May 2018 15:04:21 GMT

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-data-AKA-generate/ta-p/258712 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.