find maximum value within a range with conditions

[ZhihanZhou]

Hello everyone,

i want to find maximum values within a range and base on the maximum value creat a new column. Here as an example and my code, 

id	zweck	lead
1	4	284
1	6	54
1	7	0
1	5	-13
1	1	167
1	2	56
1	7	0
2	2	201
2	1	87
2	7	0

data Hz.y;
set Hz.WegeketteP3lead;
by lead;
retain max_val;
drop max_val;
if lead ne 0 then max_val=lead;
else do;
max_val=max(maxval,lead);
maxLead=max_val;
end;

if max_val then do zweckH=catx('-',zweck,7);
end;
run;

what i want is to find the maximum value between the number before 0 and the maximum value stay just in the same row as orignal like following, at the meantime a new created column 'zweckH' will get the 'zweck'  from the row of 'mal_val' and plus7 till 'zweck' is 7:

id	zweck	lead	max_val	zweckH
1	4	284	284	4-7
1	6	54	0	4-7
1	7	0	0	4-7
1	5	-13	0	1-7
1	1	167	167	1-7
1	2	56	0	1-7
1	7	0	0	1-7
2	2	201	201	2-7
2	1	87	0	2-7
2	7	0	0	2-7

[mkeintz]

Read each group twice (where "group" means every record until either the last of the id, or until lead=0).  In the first group-read, determine the max lead value, and the corresponding zweck. 

Prior to the second read establish zweckh.

Then, in the second pass, reread and test each observation to see lead matches the max value, and adjust the max_val variable accordingly ...and output the observations.

data want (drop=_:);
  do until (last.id=1 or lead=0);
    set have;
    by id;
    _max=max(_max,lead);
    length zweckh $8;
    if lead=_max then zweckh=cats(zweck);
  end;
  zweckh=catx('-',zweckh,zweck);

  do until (last.id=1 or lead=0);
    set have;
    by id;
    if lead<_max then max_val=0;
    else max_val=_max;
    output;
  end;
run;

Please provide your sample data in the form of a working data step if you want responses with tested code.  Respondents did that task for you in your previous request, but now it would be worth your time to do it yourself.

[ZhihanZhou]

Hello mkeintz,

It works great and simple to unterstand. In addition, I have a question about a condition in this sentence ‘ do until (last.id=1 or lead=0);’, the loop will run till the end of the table which lead by id, so here ‘last.Id=1’ means the value of last.id is 1 or last.if on the first row? Because I’ve tried both situations change or without change the number ‘1’, the result I got are the same and right.

[CurtisMackWSIPP]

Since mkeintz hasn't answered you follow-up question I'll try to answer.

The last.id syntax is returns a 1 if this is the last row with the current value of id. It returns 0 otherwise. This goes along with the "by id" statement that states the data is sorted by the id variable. So the loop runs until this is the last row with a given value for id, or lead=0.

[ZhihanZhou]

Hello CurtisMackWSIPP,

Thanks for your great answer!