Sig,

When Boolean express, x='z' evaluate to fales, it give you a value of '0', and 0/0 is of course an error, but not like in data step, proc sql will not give you an error message and by default it set the result to '.' or missing, which is just what I need, because missing is less than any negative value. That's why max() can be used here. You can see it by not using "distinct" keyword and max(). Fortunately, SAS doesn't give any error message in the log.

I consider myself an intermediate (if not an even entry level) SAS programmer, I always use SAS-L as my #1 reference, try to learn from others, in the meantime try to share my dumb idea with others, that's why I always "reply all", and barely reply privately. I feel sorry if I had ever offended anybody.

Best regards,

Ya Huang

HERMANS1 wrote: > > 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