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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: