topic Re: Selecting max value from multiple observations in Statistical Procedures

Selecting max value from multiple observations

gencharitaci — Wed, 21 May 2014 16:53:45 GMT

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.

Thank you for your help.

Re: Selecting max value from multiple observations

stat_sas — Thu, 22 May 2014 01:10:24 GMT

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;

Re: Selecting max value from multiple observations

Haikuo — Thu, 22 May 2014 03:05:14 GMT

/* 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

Re: Selecting max value from multiple observations

Ksharp — Thu, 22 May 2014 13:00:55 GMT

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

Re: Selecting max value from multiple observations

stat_sas — Thu, 22 May 2014 18:13:08 GMT

Hi,

Can you please share your data as an attachement?

Thanks,

Re: Selecting max value from multiple observations

gencharitaci — Thu, 22 May 2014 18:19:02 GMT

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_no	bolt_no	cumvol_dob	totvol_dob
1			5.849161048
1	1	2.128927995	5.849161048
1	2	3.844296214	5.849161048
1	3	5.849161048	5.849161048
2			1.127529281
2	1	0.623753528	1.127529281
2	2	1.127529281	1.127529281
3			1.316750698
3	1	0.48772395	1.316750698
3	2	0.865866814	1.316750698
3	3	1.316750698	1.316750698

Re: Selecting max value from multiple observations

stat_sas — Thu, 22 May 2014 18:33:31 GMT

How are you getting totvol_dob?

Re: Selecting max value from multiple observations

gencharitaci — Thu, 22 May 2014 18:55:02 GMT

totvol_dob must be max of cumvol_dob

Re: Selecting max value from multiple observations

stat_sas — Thu, 22 May 2014 19:07:45 GMT

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;

Re: Selecting max value from multiple observations

gencharitaci — Fri, 23 May 2014 14:37:48 GMT

Thanks stat@sas . It worked perfectly.

Re: Selecting max value from multiple observations

Reeza — Fri, 23 May 2014 16:40:00 GMT

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;

Re: Selecting max value from multiple observations

stat_sas — Fri, 23 May 2014 16:47:01 GMT

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,