topic Re: Changing Value of Variable to Largest Value in SAS Programming

Changing Value of Variable to Largest Value

acemanhattan — Mon, 20 Jul 2015 15:10:42 GMT

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_2 99

UI_n 107

How might we do this?

Re: Changing Value of Variable to Largest Value

dcruik — Mon, 20 Jul 2015 15:59:08 GMT

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!

Re: Changing Value of Variable to Largest Value

Astounding — Mon, 20 Jul 2015 22:49:57 GMT

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

Re: Changing Value of Variable to Largest Value

Ksharp — Tue, 21 Jul 2015 13:04:26 GMT

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;

Re: Changing Value of Variable to Largest Value

dcruik — Tue, 21 Jul 2015 13:11:57 GMT

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!

Re: Changing Value of Variable to Largest Value

Ksharp — Tue, 21 Jul 2015 13:41:01 GMT

Yes. That will increase I/O reading .