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?
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.
On Sat, 3 Apr 2010 17:37:18 -0400, Arthur Tabachneck <art297@NETSCAPE.NET>
>You probably already know this but, in case not, it works error free on
>SAS 9.1.3 on Windows Server 2003.
>On Sat, 3 Apr 2010 16:43:42 -0400, Paul Dorfman <sashole@BELLSOUTH.NET>
>>Consider this primitive data set:
>>data vtest ;
>> input ID CD: $1. AMT ;
>>1 Y 10
>>2 N 20
>>2 Y 30
>>3 N 40
>>3 Y 50
>>3 Y 60
>>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
>>This results in the following SAS SQL
>>ERROR: sqlplan internal error: Cannot find symbol B, var=4, tag=0001.
>>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
>>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
>>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) ;
>>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
>>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
>>SQL function in a single SQL step.)
>>Looks like a veritable bug to me. Anyone thinks it is a feature?