maximum over multiple rows per person

[Ronein]

Hello

For each person ID I have multiple observations (multiple rows).

Target is to find max weight for each customer over his/her observations.

Following code is giving  bad results.

why?

Data rawData;
Input ID month weight;
cards;
1 1801 70
1 1802 90
1 1803 85
2 1801 90
2 1802 94
2 1803 93
3 1802 78
3 1803 80
;
run;



Data tbl1(keep=ID  max_weight) ;
set rawData;
by ID;
retain max_weight 0 ;
max_weight=max(max_weight,weight);
IF last.ID then output;
Run;

[s_lassen]

I think you forgot to initialize the MAX_WEIGHT variable when the ID changes. Or you could use a DoW loop without a retain statement:

data tbl1(keep=ID  max_weight) ;
  do until(last.id);
    set rawData;
    by ID;
    max_weight=max(max_weight,weight);
    end;
run;

[RW9 · Accepted solution]

I assume by "bad results" you mean it is not giving you what you expected it to give you?  It is doing what you ask correctly, your just not covering the whole by group, so the value gets retained all the way down, so each row is the maximum of the whole table to that point, not just within the by group.  To do the by group you need to reset your retained value on first.id:

data tbl1(keep=id  max_weight) ;
  set rawdata;
  by id;
  retain max_weight;
  if first.id then max_weight=0;
  max_weight=max(max_weight,weight);
  if last.id then output;
run;

Or if you want smaller code:

data tbl1(keep=id  max_weight) ;
  set rawData;
  by ID;
  retain max_weight;
  max_weight=ifn(first.id,weight,max(max_weight,weight));
  if last.id then output;
run;

[novinosrin]

May i ask why not a simple proc means/summary/sql?

---

@Ronein wrote:

Hello

For each person ID I have multiple observations (multiple rows).

Target is to find max weight for each customer over his/her observations.

Following code is giving  bad results.

why?

Data rawData;
Input ID month weight;
cards;
1 1801 70
1 1802 90
1 1803 85
2 1801 90
2 1802 94
2 1803 93
3 1802 78
3 1803 80
;
run;



Data tbl1(keep=ID  max_weight) ;
set rawData;
by ID;
retain max_weight 0 ;
max_weight=max(max_weight,weight);
IF last.ID then output;
Run;

---