Date:         Wed, 7 Jun 2000 20:24:30 GMT
Reply-To:     Andreas Grueninger <grueninger@IBGRUENINGER.DE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Andreas Grueninger <grueninger@IBGRUENINGER.DE>
Organization: Customer of UUNET Deutschland GmbH
Subject:      Re: Transpose Again

and look at my sql query and if you don't understand why I use your
query let it me don't know.

proc sql;
select distinct year,
       max(count*(color='r')) as count_r,
       max(count*(color='g')) as count_g,
       max(revenue*(color='r')) as rev_r,
       max(revenue*(color='g')) as rev_g
from xx
group by year

;ya.huang@AGOURON.COM (Ya Huang) wrote:

>data xx;
>input Year color $ count revenue;
>cards;
>1997     r        100       1000
>1997     g        200       2000
>1998     r        150       999
>1998     g        250       2000
>1999     r        350       3500
>1999     g        125       1250
>;
>
>options nocenter;
>
>proc sql;
>select distinct year,
>       max(count*(color='r')/(color='r')) as count_r,
>       max(count*(color='g')/(color='g')) as count_g,
>       max(revenue*(color='r')/(color='r')) as rev_r,
>       max(revenue*(color='g')/(color='g')) as rev_r
>from xx
>group by year
>;
>
>===============================
>
>OBS    YEAR    COLOR    COUNT    REVENUE
>
> 1     1997      r       100       1000
> 2     1997      g       200       2000
> 3     1998      r       150        999
> 4     1998      g       250       2000
> 5     1999      r       350       3500
> 6     1999      g       125       1250
>
>
>    YEAR   COUNT_R   COUNT_G     REV_R     REV_R
>------------------------------------------------
>    1997       100       200      1000      2000
>    1998       150       250       999      2000
>    1999       350       125      3500      1250
>
>Question: what does this (color='r')/(color='r') do?
>Think about it, and let me know if you don't understand.
>
>regards,
>
>Ya Huang
>
>
>Nigel Tufnel wrote:
>>
>> Folks:
>>
>> Sorry to hit the list again with this kind of question, but I can't seem to
>> figure out this particular transposition.  I can get halfway there (I can
>> transpose "count" or "revenue" but not both!!)
>>
>> Using the layouts below, can someone help?
>>
>> >>>Current Layout
>>
>>      Year    color    count    revenue
>>      1997     r        100       1000
>>      1997     g        200       2000
>>      1998     r        150       999
>>      1998     g        250       2000
>>      1999     r        350       3500
>>      1999     g        125       1250
>>
>> >>Required Layout
>>
>>      Year   count_r   count_g   revenue_r   Revenue_g
>>      1997   100        200       1000       2000
>>      1998   150        250        999       2000
>>      1999   350        125       3500       1250
>>      2000
>>
>> As always...thanks again!
>>
>> ________________________________________________________________________
>> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Ingenieurbuero Grueninger
Uhlbergstr. 15
72631 Aichtal (Germany)
email: grueninger@ibgrueninger.de