|Date: ||Mon, 2 Jul 2001 01:40:17 GMT|
|Reply-To: ||Jay Stevens <jay@WHITEHURST-ASSOCIATES.COM>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Jay Stevens <jay@WHITEHURST-ASSOCIATES.COM>|
|Organization: ||Giganews.Com - Premium News Outsourcing|
|Subject: ||Re: SQL question for SQL sharpies|
On Thu, 7 Jun 2001 14:10:02 -0600, "T. Kalaris"
>I have a SAS sql question which I haven't been able to code around. What I
>have are two fields, a & b, which I sum() based on a third grouping field.
>Then I calculate the ratio of suma & sumb (suma/sumb as rate group by c,
>etc.). This all goes okay until I try to eliminate ratios smaller that some
>fixed amount, say WHERE or HAVING rate >0.5. When I do this SQS sql gags
>ERROR: The following columns were not found in the contributing tables:
>Note that I can ORDER BY rate, but not use WHERE (or HAVING). This
>restriction on using calculated variables I have encountered before and have
>gotten around them by CREATE VIEW xxx AS and then running a WHERE filter
>on the view. But here I doesn't work. When I try to use WHERE with the
>view, I get the following:
>ERROR: sqlplan internal error: Cannot find symbol totqmi, var=7, tag=1. This
> message can be caused by attempting to make correlated references
> between two different items on a single FROM clause.
>Any ideas or work arounds? If anyone wants to look at the code, I can post
>that as well as post some data.
I've been absent from SAS-L so forgive me for responding to an old
You can refer to the calculated fields in SAS SQL by using the
CALCULATED keyword prior to the field name.
select c, suma/sumb as rate
from table a, table2 b
where a.key = b.key
group by c
having calculated rate > 0.2;
Hope this helps.
Jay L. Stevens
Whitehurst Associates, Inc.