Date:         Wed, 1 Nov 2006 13:25:22 -0800
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: Proc Sql question
Content-Type: text/plain; charset="us-ascii"

Hmmm--I smell a transcription error.  No doubt this is not your real
code.  Does your real code have a WHERE clause by any chance?  Those can
often effectively turn a left join into an inner join--e.g., this would
do that I think:
 
proc sql;
select a.var1
      ,b.var2
      ,c.var3
from    table1 as a join
              table2 as b on a.id=b.id join
      table3 as c on a.id=c.id left join
      table4 as d on c.id=d.id
where d.var4 = 'something'
;
run;

The problem being that the WHERE condition can only be met by rows whose
IDs appear in table4...

________________________________

From: Jeri Ji [mailto:jeri_ji@freddiemac.com] 
Sent: Wednesday, November 01, 2006 1:18 PM
To: Pardee, Roy
Subject: RE: Proc Sql question



Sorry that I just replied to him. His didn't work either. 


Jeri Ji
Credit Policy&Portfolio Management Department
Phone: 571-382-3707 



   "Pardee, Roy" <pardee.r@ghc.org> 

   11/01/2006 04:05 PM 

To
"Jeri Ji" <jeri_ji@freddiemac.com> 
cc
Subject
RE: Proc Sql question

	




Well drat--I had high hopes for that. 
  
Do please reply to the list & let us know if Sig's advice did the trick
for you... 


________________________________

From: Jeri Ji [mailto:jeri_ji@freddiemac.com] 
Sent: Wednesday, November 01, 2006 1:02 PM
To: Pardee, Roy
Subject: Re: Proc Sql question


No. Thanks. 


Jeri 


   "Pardee, Roy" <pardee.r@GHC.ORG> 
  Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> 

   11/01/2006 03:06 PM 

   Please respond to
"Pardee, Roy" <pardee.r@GHC.ORG>



To
SAS-L@LISTSERV.UGA.EDU 
cc
Subject
Re: Proc Sql question


	





Any difference if you change that last join condition to:

 table4 as d on a.id=d.id

?

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Wednesday, November 01, 2006 11:16 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Proc Sql question

Please help.

I get 100 records from the following code:

proc sql;
select a.var1
      ,b.var2
      ,c.var3
from    table1 as a join
              table2 as b on a.id=b.id join
      table3 as c on a.id=c.id);
run;

Now I want to do an outer join with another table and still return the
100 records using:
proc sql;
select a.var1
      ,b.var2
      ,c.var3
from    table1 as a join
              table2 as b on a.id=b.id join
      table3 as c on a.id=c.id left join
      table4 as d on c.id=d.id);
run;

However, I get less than 100 records. Can somebody help to tell me why
and what I should do? It seemed to me that if I use only two tables, the
left join works, but not more than 2.



Jeri