Date:         Wed, 7 Jun 2000 13:42:21 -0700
Reply-To:     Ya Huang <ya.huang@AGOURON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AGOURON.COM>
Subject:      Re: Transpose Again
Comments: To: Andreas Grueninger <grueninger@IBGRUENINGER.DE>
Content-Type: text/plain; charset=us-ascii

This simplified code works fine for this case, because all
the value are positive number. If there were any negative
value in count or revenue (in general, it is possible), it will
give you a max=0, which is not what we want, my little trick
make sure the value not needed be set to "missing", and
max function will not count them.

regards,

Ya Huang

Andreas Grueninger wrote:
>
> 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