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 |
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.