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
Comments: To: Yu Zhang <zhangyu05@gmail.com>
In-Reply-To:  <200904281953.n3SGdkS3009480@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

I think that's just SQL... when you run this as:

data a;
do a=1 to 5;
output;
end;
run;

data b;
do a=1,4;
b=100;
output;
end;
run;

proc sql;
select a,coalesce(b,99) from a natural left join b
;
 quit;

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.

-Joe

On Tue, Apr 28, 2009 at 2:53 PM, Yu Zhang <zhangyu05@gmail.com> wrote:

> Hi,
>
> 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
> 0.
>
> I am using SAS9.1 on Win. the sample code is here:
>
> data a;
> do a=1 to 5;
> output;
> end;
> run;
>
> data b;
> do a=1,4;
> b=100;
> output;
> end;
> run;
>
> proc sql;
> select * from a natural left join b
> ;
>  quit;
>
> the output I expected is:
>
> 1 100
> 2 .
> 3 .
> 4 100
> 5 .
>
> Can anyone explain why 0 was assigned to the non-matching rows?
>
> TIA.
>
> Yu
>