Date: Wed, 1 Nov 2006 17:07:13 -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 question
In-Reply-To: <OF1648E7D2.C9F9080E-ON85257219.00736B39-85257219.00737833@freddiemac.com>
Content-Type: text/plain; charset="US-ASCII"
Jeri:
Even though the query as written may not yield the result that you hope
to see, it does 'work' in the sense that it preserves the rows in the
first dataset in the transitive chain. I've appended an example that
starts with five rows. The example includes a query with the conditions
that you specified, and a more classic transitive chain of left joins.
As Ray suggests, a WHERE condition may restrict the number of rows even
in a transitive chain of left joins. Any condition that limits the
number of rows on the LHS will reduce the number of rows in the yield of
the query. See the results of the final query for example.
data table1;
input id var1;
cards;
5 1
4 2
3 3
2 4
1 5
;
run;
data table2;
input id var2;
cards;
1 1
2 2
3 3
4 4
;
run;
data table3;
input id var3;
cards;
1 4
2 3
3 2
;
run;
data table4;
input id var4;
cards;
2 4
3 5
;
run;
proc sql; select a.var1
,b.var2
,c.var3
from table1 as a LEFT join
table2 as b on a.id=b.id LEFT join
table3 as c on a.id=c.id left join
table4 as d on c.id=d.id;
quit;
proc sql; select a.var1
,b.var2
,c.var3
from table1 as a LEFT join
table2 as b on a.id=b.id LEFT join
table3 as c on b.id=c.id left join
table4 as d on c.id=d.id;
quit;
proc sql; select a.var1
,b.var2
,c.var3
from table1 as a LEFT join
table2 as b on a.id=b.id LEFT join
table3 as c on b.id=c.id left join
table4 as d on c.id=d.id
where var1>4
;
quit;
Sig
-----Original Message-----
From: Jeri Ji [mailto:jeri_ji@freddiemac.com]
Sent: Wednesday, November 01, 2006 4:01 PM
To: Sigurd Hermansen
Cc: Jeri Ji
Subject: RE: Proc Sql question
Tried, didn't work. Don't know why. Thank you.
Jeri
"Sigurd Hermansen" <HERMANS1@WESTAT.com>
11/01/2006 02:55 PM
To
"Jeri Ji" <jeri_ji@freddiemac.com>, <SAS-L@listserv.uga.edu>
cc
Subject
RE: Proc Sql question
Jeri:
For a transitive join of A X B X C, use a chain of left joins:
proc sql; select a.var1
,b.var2
,c.var3
from table1 as a LEFT join
table2 as b on a.id=b.id LEFT join
table3 as c on a.id=c.id left join
table4 as d on c.id=d.id);
run;
The sequence of joins proceeds from left to right. Once an inner
join
eliminates rows in table1 or table2 from the chain of query
yields,
those rows are gone.
Sig
-----Original Message-----
From: owner-sas-l@listserv.uga.edu
[mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Jeri Ji
Sent: Wednesday, November 01, 2006 2:16 PM
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
