Changing Value of Variable to Largest Value

[acemanhattan]

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?

[dcruik]

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!

[Astounding]

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).

[Ksharp]

Code: Program

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;

[dcruik]

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!

[Ksharp]

Yes. That will increase I/O reading .