Date:         Wed, 21 Apr 1999 15:53:43 -0400
Reply-To:     Ray Pass <raypass@WORLDNET.ATT.NET>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Ray Pass <raypass@WORLDNET.ATT.NET>
Subject:      Re: Use of a variable created in an SQL table
In-Reply-To:  <BB1B28D40434D011BB4000805FBEA07A0263E2E1@/MOKC2W9XSRVX01>
Content-Type: text/plain; charset="us-ascii"

At 12:31 PM 4/21/99 -0500, Boylan, Dave wrote:  (his entire query follows
below)
...
>Is there any way to reference a variable (column) created in an SQL step, or
>do I need to use the longer method?
...
 
The answer is YES.  In that indispensable SAS manual, Technical Report
P-222, Changes and Enhancements to Base SAS Software, Release 6.07 (no SAS
certified professional should be without it), in the PROC SQL section, the
"new" CALCULATED component is introduced (p. 284).  This does exactly what
Dave is looking for.
It allows you to reference a variable in a SELECT clause that was created
earlier in the same clause.  Just put the keyword CALCULATED in front of
the new variable.  In Dave's case, he would code:
 
   ...
   s.sales*p.price as revenue,
   calculated revenue*(1+t.tax) as revtax
   ...
 
That oughta do it.
 
Ray
 
 
 
 
 
>I'm having trouble figuring out how to use a variable created in an PROC
>SQL.  I am creating the variable "revenue" in an SQL statement and would
>like to use it to create another variable (revtax).  However, once I create
>a variable
>
>    s.sales*p.price as revenue,
>
>I can't reference it,
>
>    revenue*(1+t.tax) as revtax -> ERROR: The following columns were not
>found in the contributing tables: REVENUE.
>
>To calculate RevTax, I have to write:
>
>s.sales*p.price * (1+t.tax) as revtax - >(longer method)
>
>Is there any way to reference a variable (column) created in an SQL step, or
>do I need to use the longer method?
>
>Sample code:
>Proc SQL;
>    create table revenue as
>    select
>        s.id,
>          s.sales,
>        p.price
>        s.sales*p.price as revenue,
>     /* revenue*(1+t.tax) as revtax  ---> does not work!!  */
>        s.sales*p.price*(1+t.tax) as revtax /* need to use this approach */
>        from sales as s
>        inner join price as p
>            on (s.id=p.id)
>        inner join tax as t
>            on (s.id=t.id)
>    ;
>Quit;
>
>Thanks for your help.
>
>
>--------------------------------------
>David Boylan
>Corporate Forecasting
>UtiliCorp United, Inc.
>816.467.3062
>--------------------------------------
 
*------------------------------------------------*
| Ray Pass                voice: (914) 693-5553  |
| Ray Pass Consulting       fax: on request      |
| 5 Sinclair Place                               |
| Hartsdale, NY 10530    e-mail: raypass@att.net |
*------------------------------------------------*