topic Re: Finding the max value in a group and assigning it in SAS Programming

Finding the max value in a group and assigning it

jaycieleung — Thu, 11 Mar 2021 07:05:26 GMT

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.

Re: Finding the max value in a group and assigning it

ChrisNZ — Thu, 11 Mar 2021 07:14:38 GMT

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

Re: Finding the max value in a group and assigning it

jaycieleung — Thu, 11 Mar 2021 08:05:41 GMT

That worked! thanks very much!