topic Re: maximum over multiple rows per person in SAS Programming

maximum over multiple rows per person

Ronein — Mon, 26 Nov 2018 10:14:18 GMT

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;

Re: maximum over multiple rows per person

s_lassen — Mon, 26 Nov 2018 10:40:12 GMT

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;

Re: maximum over multiple rows per person

RW9 — Mon, 26 Nov 2018 10:44:04 GMT

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;

Re: maximum over multiple rows per person

novinosrin — Mon, 26 Nov 2018 12:12:03 GMT

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;