Date:         Thu, 26 Oct 2006 18:51:43 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: a special sql query
Comments: To: toby989@HOTPOP.COM

Toby,

I'm not sure what you're asking for.  It sounds, though, like you first
want to add grp to all records based on A.  If so, that part should be
easy.  For example,

data have;
  input A 1.  @5 grp  $1.  @8 B;
  cards;
1   a      0.5
2   a      0.4
3   b      0.4
4   b      0.6
5   c      0.7
6   c      0.8
7   c      0.5
1          0.1
2          0.2
3          0.2
4          0.3
5          0.2
6          0.4
7          0.3
;
run;
proc sql;
  create table want as
    select a, max(grp) as grp, b
   from have
     group by a;
quit;

But, if the above is correct, I'm not sure what you want to do from there.

Art
---------
On Thu, 26 Oct 2006 14:39:01 -0700, toby989@HOTPOP.COM wrote:

>Hi All
>
>Dont know if the SAS group is appropriate for that posting, but I am
trying to
>get some numbers calculated from a table using sql.
>
>A   grp    B
>1   a      0.5
>2   a      0.4
>3   b      0.4
>4   b      0.6
>5   c      0.7
>6   c      0.8
>7   c      0.5
>1          0.1
>2          0.2
>3          0.2
>4          0.3
>5          0.2
>6          0.4
>7          0.3
>
>I want the average of B where A is 1a|2a divided by the average of B
where A is
>1|2, and I want that for each letter (ie group by). Does that make sense?
Ie the
>problem is that the grouping information is only in the table for the
first
>'variable'.
>
>
>
>(note, I dont want to transpose, and prefer to have all in one single SQL
create
>table statement)
>
>I tried something like
>
>
>
>create table as X as A, avg(B)/(select avg(B) from tbl where X=A and
grp='')
>from tbl where grp~='' group by grp
>
>
>, but the 'group by obviously does not extend into the inner select
clause. Or
>the X is not available to the where clause of the inner select.
>
>Thanks Toby