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 |
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.