Date:         Thu, 2 Nov 2006 11:14:37 -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
Comments: To: Jeri Ji <jeri_ji@freddiemac.com>
In-Reply-To:  <CA8F89971ADA9F47A6C915BA2397844203713B79@MAILBE2.westat.com>
Content-Type: text/plain; charset="US-ASCII"

Jeri:
Perhaps due to its format, this reply didn't go thru so far as I can
see. Apologies if anyone receives a duplicate.
Sig
-----Original Message-----
From: Sigurd Hermansen
Sent: Wednesday, November 01, 2006 6:42 PM
To: 'Jeri Ji'
Cc: SAS-L@listserv.uga.edu
Subject: RE: Proc Sql question


Jeri:
SQL makes it easy to expand a table definition and insert a subsetting
condition in line:
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
(select * from table4 where <put condition here>) as d on c.id=d.id;
quit;
Sig
-----Original Message-----
From: Jeri Ji [mailto:jeri_ji@freddiemac.com]
Sent: Wednesday, November 01, 2006 5:41 PM
To: Sigurd Hermansen
Cc: Jeri Ji; SAS-L@listserv.uga.edu
Subject: RE: Proc Sql question



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>
SubjectRE: 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
SubjectRE: 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