Contributor
Posts: 46

# Changing Value of Variable to Largest Value

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?

Frequent Contributor
Posts: 132

## Re: Changing Value of Variable to Largest Value

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!

Super User
Posts: 6,785

## Re: Changing Value of Variable to Largest Value

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

Super User
Posts: 10,787

## Re: Changing Value of Variable to Largest Value

### Code: Program

`data have;input ID\$ Weight;datalines;UI_1 121UI_1 122UI_1 125UI_2 99UI_2 95UI_3 101UI_3 102UI_3 107;run; proc sql;create table max_weight(drop=weight) asselect *, max(Weight) as max_weightfrom havegroup by ID;quit;`
Frequent Contributor
Posts: 132

## Re: Changing Value of Variable to Largest Value

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!

Super User
Posts: 10,787

## Re: Changing Value of Variable to Largest Value

Yes. That will increase I/O reading .

Discussion stats
• 5 replies
• 292 views
• 1 like
• 4 in conversation