Date: Thu, 26 Oct 2006 17:30:48 -0700
Reply-To: toby989@HOTPOP.COM
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: toby989@HOTPOP.COM
Subject: Re: a special sql query
In-Reply-To: <200610262251.k9QIeOJG030855@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Hi
I solved it, maybe I did it in an awkward way and it can be optimized.
I first created 2 tables from this one table, and then merged them together
again and then did my group by calculations.
create table out1 as select distinct 'ZZ'||substr(t1._name_,4,2) as _name_,
avg(l)/avg(u) as _estim_ from (select _name_, _estim_ as l from outcfa where
substr(_name_,1,1)='l') as t1, (select _name_, _estim_ as u from outcfa where
substr(_name_,1,1)='u') as t2 where substr(t1._name_,2,2)=substr(t2._name_,2,2)
group by substr(t1._name_,4,2);
Toby
Arthur Tabachneck wrote:
> 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
| 
