Programming the statistical procedures from SAS

Selecting max value from multiple observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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.

Capture.JPG

Any feedback would be greatly appreciated.

Thank you for your help.


Accepted Solutions
Solution
‎05-22-2014 03:07 PM
Trusted Advisor
Posts: 1,222

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,222

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;

Trusted Advisor
Posts: 1,222

Re: Selecting max value from multiple observations

Hi,

Can you please share your data as an attachement?

Thanks,

Respected Advisor
Posts: 3,147

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
Trusted Advisor
Posts: 1,222

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
Trusted Advisor
Posts: 1,222

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: 18,569

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;

Trusted Advisor
Posts: 1,222

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: 9,775

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 920 views
  • 0 likes
  • 5 in conversation