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 .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.