topic Re: maximum value by group an keep this as a new column for each group in SAS Enterprise Guide

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

LeaSt — Fri, 31 May 2019 09:36:13 GMT

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

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

PeterClemmensen — Fri, 31 May 2019 09:46:35 GMT

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;

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

LeaSt — Fri, 31 May 2019 10:12:23 GMT

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

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

PeterClemmensen — Fri, 31 May 2019 11:08:23 GMT

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

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

Ksharp — Fri, 31 May 2019 13:11:55 GMT

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;