Date: Wed, 1 Nov 2006 17:41:02 -0500
Reply-To: Jeri Ji <jeri_ji@FREDDIEMAC.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jeri Ji <jeri_ji@FREDDIEMAC.COM>
Subject: Re: Proc Sql question
In-Reply-To: <CA8F89971ADA9F47A6C915BA2397844203713B78@MAILBE2.westat.com>
Content-Type: text/plain; charset="US-ASCII"
Yes, I do have WHERE condition for the 4th table. I think that is why.
However, what happened to the 4th table is that one ID has two records,
one is valid and the other is not. I will have to chose the valid one. Any
solution to that?
My temporary solution is to get what I need from the 4th table using a
separate proc sql and use data step to merge the two datasets.
Thank you very much for all your help.
Jeri
"Sigurd Hermansen" <HERMANS1@WESTAT.com>
11/01/2006 05:07 PM
To
"Jeri Ji" <jeri_ji@freddiemac.com>
cc
<SAS-L@listserv.uga.edu>
Subject
RE: Proc Sql question
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
