## Selecting max value from multiple observations

Solved
Occasional Contributor
Posts: 8

# Selecting max value from multiple observations

I have a table with tree_no, cumvol_dob variables.

I would like to know how to get the totvol_dob variable from cumvol_dob by tree_no in data step.

Any feedback would be greatly appreciated.

Accepted Solutions
Solution
‎05-22-2014 03:07 PM
Posts: 1,258

## Re: Selecting max value from multiple observations

Try this code and replace "have" with your dataset name.

proc sort data=have out=one;
by tree_no cumvol_dob;
run;

data two(keep=tree_no totvol_dob);
set one;
by tree_no;
totvol_dob=cumvol_dob;
if last.tree_no then output;
run;

data want;
merge have two;
by tree_no;
run;

proc print data=want;
run;

All Replies
Posts: 1,258

## Re: Selecting max value from multiple observations

Hi,

I am assuming that "have" is the name of above data set. Try this for the desired solution.

Thanks,

data one;
set have;
flag = (tree_no=1);
if flag=1 then n+1;
run;

proc sort data=one out=two;
by n cumvol_dob;
run;

data three (keep=n totvol_dob);
set two;
by n cumvol_dob;
totvol_dob=cumvol_dob;
if last.n then output;
run;

data want(drop=n flag);
merge one three;
by n;
run;

Posts: 1,258

Hi,

Thanks,

Posts: 3,157

## Re: Selecting max value from multiple observations

/* To make a group variable*/

data have;

set have;

group+(var-lag(tree_no) ne 1);

run;

proc sql;

create table want as

select *, max(cumvol_dob) as totvol_dob

from test

group by tree_no;

quit;

Haikuo

Occasional Contributor
Posts: 8

## Re: Selecting max value from multiple observations

I found a mistake on my first post. I am sorry for that and thanks for your understanding.

How can I get to the totvol_dob variable from cumvol_dob as shown below?

tree_nobolt_nocumvol_dobtotvol_dob
15.849161048
112.1289279955.849161048
123.8442962145.849161048
135.8491610485.849161048
21.127529281
210.6237535281.127529281
221.1275292811.127529281
31.316750698
310.487723951.316750698
320.8658668141.316750698
331.3167506981.316750698
Posts: 1,258

## Re: Selecting max value from multiple observations

How are you getting totvol_dob?

Occasional Contributor
Posts: 8

## Re: Selecting max value from multiple observations

totvol_dob must be max of cumvol_dob

Solution
‎05-22-2014 03:07 PM
Posts: 1,258

## Re: Selecting max value from multiple observations

Try this code and replace "have" with your dataset name.

proc sort data=have out=one;
by tree_no cumvol_dob;
run;

data two(keep=tree_no totvol_dob);
set one;
by tree_no;
totvol_dob=cumvol_dob;
if last.tree_no then output;
run;

data want;
merge have two;
by tree_no;
run;

proc print data=want;
run;

Occasional Contributor
Posts: 8

## Re: Selecting max value from multiple observations

Thanks  . It worked perfectly.

Super User
Posts: 20,716

## Re: Selecting max value from multiple observations

Proc sql is also great for this.

Just a caution, if the cumvol_dob that is the max isn't the last records, which it always is in the example data then stat@sas  code above won't work.

proc sql;

create table want as

select a.*, max(cumvol_dob) as totvol_dob

from have

group by tree;

quit;

Posts: 1,258

## Re: Selecting max value from multiple observations

Thanks for the input Reeza. I coded this based on attached data.xls file. Actually data given above is different from the attached file.

Regards,

Super User
Posts: 10,205

## Re: Selecting max value from multiple observations

```data have;
input a b ;
cards;
1 .
2 3.2
3 4.4
4 5.4
1 .
2 3.2
;
run;
data have;
set have;
if a=1 then group+1;
run;
data want;
do until(last.group);
set have;
by group;
end;
tot=b;
do until(last.group);
set have;
by group;
output;
end;
drop group;
run;

```

Xia Keshan

🔒 This topic is solved and locked.