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.
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.
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.