Date: Tue, 28 Apr 2009 15:05:56 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: SQL:Left join
Content-Type: text/plain; charset=ISO-8859-1
I think that's just SQL... when you run this as:
do a=1 to 5;
select a,coalesce(b,99) from a natural left join b
you get 99's, so it's definitely behaving normally up to that point (it
would return 0 not 99 if it considered the value not 'null'). Apparently
SAS SQL considers null to be 0... no idea why.
On Tue, Apr 28, 2009 at 2:53 PM, Yu Zhang <email@example.com> wrote:
> I am really confusing now when I was using left join to combine two
> datasets. with my past experience, the variable from Right had side table
> will be missing for any non-matching row. But it was assigned with value of
> I am using SAS9.1 on Win. the sample code is here:
> data a;
> do a=1 to 5;
> data b;
> do a=1,4;
> proc sql;
> select * from a natural left join b
> the output I expected is:
> 1 100
> 2 .
> 3 .
> 4 100
> 5 .
> Can anyone explain why 0 was assigned to the non-matching rows?