BookmarkSubscribeRSS Feed
saslearner321
Calcite | Level 5

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
    
2 REPLIES 2
novinosrin
Tourmaline | Level 20
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;
ballardw
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2092 views
  • 0 likes
  • 3 in conversation