Finding the max value in a group and assigning it

[jaycieleung]

Hi, I'm new to SAS and I'm looking for a way to assign the maximum value of a group to the MaxInGroup column like below:

Group | Value | MaxInGroup (the resulting column)

A         | 1        | 2

A         | 2        | 2

B         | 2        | 4

B         | 3        | 4

B         | 4        | 4

C         | 5        | 5 

What I did is to use the proc sort statement to arrange the Group and Value column in descending order and if first.Group=1 and first.Value=1, assign MaxInGroup = Value. But I'm stuck at how to assign the same MaxInGroup value for the rest of the group.

Thanks a lot.

[ChrisNZ · Accepted solution]

Like this?

proc sql;
  create table WANT as 
  select *, max(VAR) as MAX
  from TABLE
  group by ID;
quit;

No need for a prior sort

[jaycieleung]

That worked! thanks very much!