maximum value by group an keep this as a new column for each group

[LeaSt]

Hello, 

I want to calculate the maximum of a column by group (ID) and save it in a new column.

So my Data is:

ID  X   

1   4     

1   3    

1   2     

2   1     

2   5     

And I want to have:

ID  X   max_X

1   4       4

1   3       4

1   2       4

2   1       5

2   5       5

I tried:

proc sql;
create table work.max as
select id as id,
max(X) as max_X
from work.data
group by id;
quit;

But if I tried this I just get:

ID  X   max_X

1   4       4

2   5       5

I hope somebody have a nice idea to solve my problem! 

Thank you in advance!

Lea

[PeterClemmensen · Accepted solution]

Hi and welcome to the SAS Communities 🙂

Here are two different approaches

data data;
input ID X; 
datalines;
1 4 
1 3 
1 2 
2 1 
2 5 
;

/* Data step method */
data datastepMethod;  
   max_X=.;
   do until (last.ID);
      set data;
      by ID;
      if X gt max_X then max_X=X;
   end;
   do until (last.ID);
      set data;
      by ID;
      output;
   end;
run;

/* Proc SQL method */
proc sql;
create table SQLMethod as
   select id,
          (select max(X) as max_X from data where a.id=id group by ID) as max_X
   from data as a;
quit;

[LeaSt]

Thank you for the quick response! I used the data step method and it works perfectly.

[PeterClemmensen]

That't my favorite too 🙂 Glad you found your answer.

[Ksharp]

data data;
input ID X; 
datalines;
1 4 
1 3 
1 2 
2 1 
2 5 
;

proc sql;
select *,max(x) as max_x
 from data
  group by id;
quit;