Date:         Sun, 4 Apr 2010 01:37:26 -0400
Reply-To:     Paul Dorfman <sashole@BELLSOUTH.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul Dorfman <sashole@BELLSOUTH.NET>
Subject:      Re: SQL view bug?
Comments: To: Arthur Tabachneck <art297@NETSCAPE.NET>

Art,

No, I did not know it - I no longer have access to SAS 9.1 on any
platform. Your finding indicates that there has been one stark improvement
in 9.2 vs 9.1.

Kind regards
------------
Paul Dorfman
Jax, FL
------------

On Sat, 3 Apr 2010 17:37:18 -0400, Arthur Tabachneck <art297@NETSCAPE.NET>
wrote:

>Paul,
>
>You probably already know this but, in case not, it works error free on
>SAS 9.1.3 on Windows Server 2003.
>
>Art
>--------
>On Sat, 3 Apr 2010 16:43:42 -0400, Paul Dorfman <sashole@BELLSOUTH.NET>
>wrote:
>
>>Consider this primitive data set:
>>
>>data vtest ;
>>   input ID CD: $1. AMT ;
>>cards ;
>>1 Y 10
>>2 N 20
>>2 Y 30
>>3 N 40
>>3 Y 50
>>3 Y 60
>>;
>>run ;
>>
>>and even more primitive SAS SQL code run against it:
>>
>>proc sql ;
>>   create view vtest_view as
>>   select ID
>>        , CD = "Y"                   as A
>>        , calculated A and (AMT > 5) as B
>>   from   vtest
>>   ;
>>   create table vtest_sum as
>>   select ID
>>        , sum (A) as SUM_A
>>        , sum (B) as SUM_B
>>   from   vtest_view
>>   group  ID
>>   ;
>>quit ;
>>
>>This results in the following SAS SQL
>>
>>ERROR: sqlplan internal error: Cannot find symbol B, var=4, tag=0001.
This
>>message can be caused by attempting to make correlated references between
>>two different items on a single FROM clause.
>>
>>This happens under both Windows adn AIX UNIX running the same 9.2.
>>Although I kind of comprehend what the message alludes to, I still fail
to
>>see how that can be an excuse for 2 reasons.
>>
>>First, the error message disappears, and the processing completes error-
>>free, if instead of the *view* VTEST_VIEW I create a *table* and
reference
>>it in the ensuing statement.
>>
>>Second, if I create a completely equivalent *DATA step* view, say
>>
>>data ds_view / view = ds_view ;
>>   set vtest ;
>>   A = (CD = "Y") ;
>>   B = (A and AMT > 5) ;
>>run ;
>>
>>and let the summarizing step consume it instead of the *SQL view*, it has
>>no problem running normally, either.
>>
>>(If anyone is interested why I need a view in the first place, in the
real
>>life the mocked-up CD="Y" condition is *much* more involved, and I do not
>>want SAS to sift through the computations twice for performance reasons -
>>otherwise I would simply make the boolean expressions arguments of the
SUM
>>SQL function in a single SQL step.)
>>
>>Looks like a veritable bug to me. Anyone thinks it is a feature?
>>
>>Kind regards
>>------------
>>Paul Dorfman
>>Jax, FL
>>------------