Date: Wed, 7 Jun 2000 16:58:46 -0400 HERMANS1 "SAS(r) Discussion" HERMANS1 Re: Transpose Again To: Ya Huang text/plain; charset=US-ASCII

Still thinking in general terms, what happens if the Boolean expression, x='z', evaluates to false (no instances present in table)? Would that in SAS SQL translate to 0 and, if you divide by that number, produce a run-time error? Besides, I don't think that a Boolean expression in SAS can evalute to a negative number. I like the idea of a more general solution and find your solution clever, but in this case I'd have to call it "too clever by half". Sig ____________________Reply Separator____________________ Subject: Re: Transpose Again Author: Ya Huang <ya.huang@AGOURON.COM> Date: 6/7/2000 1:42 PM

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

Back to: Top of message | Previous page | Main SAS-L page