Selecting max value from multiple observations

[gencharitaci]

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.

[stat_sas]

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;

[stat_sas]

Hi,

Can you please share your data as an attachement?

Thanks,

[Haikuo]

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

[gencharitaci]

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

[stat_sas]

How are you getting totvol_dob?

[gencharitaci]

totvol_dob must be max of cumvol_dob

[stat_sas · Accepted solution]

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;

[gencharitaci]

Thanks  stat@sas . It worked perfectly.

[Reeza]

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;

[stat_sas]

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,

[Ksharp]

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