Date:         Wed, 21 Apr 1999 12:31:41 -0500
Reply-To:     "Boylan, Dave" <dboylan@UTILICORP.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Boylan, Dave" <dboylan@UTILICORP.COM>
Subject:      Use of a variable created in an SQL table
Content-Type: text/plain

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
--------------------------------------