|
Proc summary does somewhat more concisely. PROC SUMMARY does have
limitations that may make the other methods more desirable.
proc summary data=foo nway;
class id;
output out=work.who
idgroup(max(date) min(weight) max(height) last obs out(rowID
date weight height magic)=)
;
run;
proc print;
run;
Of the 4 observations with tied "height, weight, date" how did you
decide to choose the LAST. I added LAST option to IDGROUP because it
chooses first by default.
76 1 6 70 2.0 76 0.46007
77 1 6 70 2.0 77 0.02628
78 1 6 70 2.0 78 0.71653
79 1 6 70 2.0 79 0.84948
80 1 6 70 2.0 80 0.60564
On Thu, Apr 17, 2008 at 8:03 AM, Richard A. DeVenezia
<rdevenezia@wildblue.net> wrote:
> Tree Frog wrote:
> > Thanks Howard
> >
> > So I'd need to nest as many inline views as I had variables to
> > aggregate over in order to select a row? There'd be a point where a
> > sort-and-by-group-process-in-a-data-step would probably be more
> > efficient (not to mention easier to code and comprehend from code)...
> > but sorting is so expensive. Any other ideas?
>
> Yes. Consider this sample code in which the 'magic' for each groups newest,
> lightest, tallest is determined.
> A sort way and SQL way is shown. Another way (not shown) is using the DATA
> Step hash object.
>
> Clarity of problem statement (business rule) is extremely important when
> nested hierarchies of criteria are involved.
>
> -----------------------
> data foo;
> do id = 1 to 5;
> do date = 1 to 5;
> do _r1 = 1 to 5;
> weight = 70 + floor(30*ranuni(123));
> height = 1.5 + round(3*ranuni(123),.1);
> rowid+1;
> magic = ranuni(123);
> output;
> end;
> end;
> date = 6;
> do weight = 71 to 75;
> do _r2 = 1 to 5;
> height = 1.5 + round(3*ranuni(123),.1);
> rowid+1;
> magic = ranuni(123);
> output;
> end;
> end;
> weight = 70;
> do height = 1.5 to 2.0 by 0.1;
> do _r3 = 1 to 5;
> rowid+1;
> magic = ranuni(123);
> output;
> end;
> end;
> end;
>
> drop _:;
> run;
>
> proc sort data=foo out=bar;
> by
> id
> descending date
> weight
> descending height
> descending rowid
> ;
> run;
>
> data magic;
> set bar;
> by
> id
> descending date
> weight
> descending height
> ;
> if first.id and first.date and first.weight and first.height;
> run;
>
> proc sql;
> create table magic_also as
> select * from
> (
> select * from
> (
> select * from
> (
> select * from foo
> group by id, date, weight
> having height = max(height)
> )
> having weight = min(weight)
> )
> having date = max(date)
> )
> group by id
> having rowid = max(rowid)
> ;
> quit;
> -----------------------
>
>
> Richard A. DeVenezia
> http://www.devenezia.com
>
|
