Date:         Thu, 17 Apr 2008 16:46:02 -0700
Reply-To:     Tree Frog <tree.frog2@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Tree Frog <tree.frog2@HOTMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: SQL: selecting rows based on group aggregate value
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

Thanks very much for this...

Just a quick question: Richard, in the 'magic' data step, isn't
everything after "if first.id" redundant?

TF

On Apr 17, 11:03 pm, rdevene...@WILDBLUE.NET ("Richard A. DeVenezia")
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. DeVeneziahttp://www.devenezia.com