Date:         Mon, 31 Jan 2005 11:41:05 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Proc SQL Anomoly When Using Case Statements
Comments: To: "Miller, John" <millerj@WSIPP.WA.GOV>
Content-Type: text/plain; charset="windows-1255"

John:
Intriguing that this works as you might expect (V9.1.3 and perhaps earlier
versions as well):
   ....
    BeginDate,

    EndDate,

       %*-> Note that the next three lines comprise one statement;

    case when 1 eq 1 then EndDate*1 else EndDate*1 end -

        case when 1 eq 1 then BeginDate*1 else BeginDate*1 end

                    as WhenCaseStatementsAreInPlay,

      %*-> Perform identical calculation as above, but get different
results;

    EndDate - BeginDate as WhenCaseStatementsAreNotInPlay ....

I suspect that any expression that yields a value of numeric type will
identify the type of the computed value. Each of the original CASE
statements yields a value of unknown type since the SQL compiler cannot
(does not?) assume that numeric columns in the data sources will become
numeric columns in the yield of the query.

The special conditions of the test make it a difficult one for the compiler.
I would agree that the SAS SQL compiler should handle this case correctly
without us having to provide hints for the compiler. Even so, I have to
wonder whether useful case expressions will ever resolve to anything as
simple as the test case.
Sig

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Miller,
John
Sent: Friday, January 28, 2005 9:01 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Proc SQL Anomoly When Using Case Statements


I have encountered a problem I suspect is an error within Proc SQL.  I am
using SAS 9.1 on WinXP Professional.   I invite you to run the following
example.  My results are depicted at the bottom.  If your results differ, or
if you can point out something I am doing wrong, or misunderstanding, I
would be grateful.  I would much prefer the problem is mine rather than
something that would cause us to have to review all of our SQL.



Thanks for your help.



data Example;

  length BeginDate EndDate 4; *<-- Experiment with other lengths;

  BeginDate = '01Jan2005'd;

  EndDate   = '31Jan2005'd;

  format BeginDate EndDate date9.;

run;



title1 "This demonstrates an apparent anomaly when employing case
statements";

title2 "with integer values in numeric fields that are less than 8 bytes in
length.";

title3 "Note that fields WhenCaseStatementsAreInPlay and
WhenCaseStatementsAreNotInPlay";

title4 "are created with different logic, but are created logically
identical.";

title5 "Changing the length statement in the datastep reveals that only
when";

title6 "the BeginDate and EndDate are of length 8 will the correct result
be";

title7 "rendered by use of the case statements.";

proc sql;

  select

    BeginDate,

    EndDate,



      %*-> Note that the next three lines comprise one statement;

    case when 1 eq 1 then EndDate else EndDate end -

         case when 1 eq 1 then BeginDate else BeginDate end

             as WhenCaseStatementsAreInPlay,



      %*-> Perform identical calculation as above, but get different
results;

    EndDate - BeginDate as WhenCaseStatementsAreNotInPlay

 from Example

  ;

quit;

title;



My output:



This demonstrates an apparent anomaly when employing case statements

with integer values in numeric fields that are less than 8 bytes in length.

Note that fields WhenCaseStatementsAreInPlay and
WhenCaseStatementsAreNotInPlay

are created with different logic, but are created logically identical.

Changing the length statement in the datastep reveals that only when

the BeginDate and EndDate are of length 8 will the correct result be

rendered by use of the case statements.



                          WhenCase      WhenCase

                        Statements    Statements

BeginDate    EndDate     AreInPlay  AreNotInPlay

ャャャャャャャャャャャャャャャャャャャャャャャャ

01JAN2005  31JAN2005      000E-308            30



-----------------------------------------------------------------------

John Miller

Washington State Institute for Public Policy

110 East Fifth Avenue, Suite 215

Olympia, WA 98504-0999

(360) 586-9436

millerj@wsipp.wa.gov

website: www.wsipp.wa.gov <http://www.wsipp.wa.gov/>

-----------------------------------------------------------------------