Say we have patients whose weight we note periodically and at the end of the year we want to change the value of their weight reading in each observation to the highest valued reading for that patient over the whole year. So,
Have
Unique Identifier Weight
UI_1 121
UI_1 122
UI_1 125
UI_2 99
UI_2 95
.
.
.
UI_n 101
UI_n 102
UI_n 107
Want
Unique Identifier Weight
UI_1 125
UI_1 125
UI_1 125
UI_2 99
UI_2 99
.
.
.
UI_n 107
UI_n 107
UI_n 107
How might we do this?
Here's a way to do it in two steps, but there may be a way to use by processing and do it in one.
data have;
input ID$ Weight;
datalines;
UI_1 121
UI_1 122
UI_1 125
UI_2 99
UI_2 95
UI_3 101
UI_3 102
UI_3 107
;
run;
proc sql;
create table max_weight as
select ID, max(Weight) as max_weight
from have
group by ID;
quit;
proc sql;
create table want as
select a.ID, max_weight as Weight
from have a left join max_weight b
on (a.ID=b.ID);
quit;
Hope this helps!
Yes, here's the method you alluded to, using BY processing in one step:
data want;
do until (last.id);
set have;
by id;
max_weight = max(weight, max_weight);
end;
do until (last.id);
set have;
by id;
output;
end;
run;
The top loop finds the max value for weight, for an ID. Then the bottom loop reads the same records and outputs them (automatically including the max_weight).
data have;
input ID$ Weight;
datalines;
UI_1 121
UI_1 122
UI_1 125
UI_2 99
UI_2 95
UI_3 101
UI_3 102
UI_3 107
;
run;
proc sql;
create table max_weight(drop=weight) as
select *, max(Weight) as max_weight
from have
group by ID;
quit;
Xia,
I was hoping you could answer a quick question I've had with remerging summary statistics back with the original data set. Does this cause an increase in I/O due to reading the data more than once (first to max the weight within the by group and second to merge back against the have data set)? Obviously, with a small data set like this it doesn't matter, but if it was with large data would it have much effect? Just wondering for the future...
Thanks!
Yes. That will increase I/O reading .
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.