Date:         Wed, 27 Apr 2011 19:26:51 -0400
Reply-To:     Gabriel Rosas <rosas.gabe@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Gabriel Rosas <rosas.gabe@GMAIL.COM>
Subject:      Re: ^missing() and var ^='' behave differently for Oracle libname
              engine?
Comments: To: Ya Huang <ya.huang@amylin.com>
In-Reply-To:  <201104272320.p3RKM7wY026718@willow.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

That Oracle treats nulls differently would be my assumption. Have you tried
WHERE reporter_type_desc IS NOT NULL ?
On Apr 27, 2011 7:22 PM, "Ya Huang" <ya.huang@amylin.com> wrote:
> Hi there,
>
> Can someone explain why the following get totally different result?
>
> 29 proc sql;
> 30 create table x1 as
> 31 select *
> 32 from MYORAC.LSS_ESD_RPT_REPORTERS
> 33 where ^missing(reporter_type_desc)
> 34 ;
> NOTE: Table WORK.X1 created, with 110929 rows and 46 columns.
>
> 35 create table x2 as
> 36 select *
> 37 from MYORAC.LSS_ESD_RPT_REPORTERS
> 38 where reporter_type_desc ^=''
> 39 ;
> NOTE: Table WORK.X2 created, with 0 rows and 46 columns.
>
> Myorac is a Oracle libname engine.
>
> The only difference between the two is the way to specify
> a character variable is not missing. Obviously something is wrong
> for the second query. Is it because Oracle treat character missing
> differently from SAS?
>
> Thanks
>
> Ya