Sylvia Sylvia - 1 month ago 4
SQL Question

How to get max of the rows above in each group(Sql or SAS)

Data:

ID step order
100 1 1
100 2 2
100 3 3
100 1 4
200 2 5
200 3 6
200 1 7


Desired Result( I want to get the max of the rows above in each group)

ID step max_step
100 1 1
100 2 2
100 3 3
100 1 3
200 2 2
200 3 3
200 1 3


Thank you very much!:)

Joe Joe
Answer

If you want to have some idea of row order, then SAS is going to be the easier answer here.

data want;
  set have;
  by ID;
  retain max_step;
  if first.id then call missing(max_step);
  max_step = max(step,max_step);
run;
Comments