Prev: SAS-to-Excel Oddity II
Next: How to get Individual Region value from the macro list ???????
From: Sigurd Hermansen on 12 Sep 2006 15:56
Andrew:
.... left join (select * from tblTrans_All where tran_date >
'15JUN2006'd) as b ....

takes the guesswork out and works efficiently as well.
Sig

-----Original Message-----
From: owner-sas-l(a)listserv.uga.edu [mailto:owner-sas-l(a)listserv.uga.edu]
On Behalf Of Andreww
Sent: Tuesday, September 12, 2006 12:07 PM
To: sas-l(a)uga.edu
Subject: proc sql & left join & where statement


I have a table tblCustomers_new and transactions tblTrans_all

What I want to do is pull out all my new customers (tblCustomers_New)
trans.... but only where trans_date > '15JUN2006'

If I do:

proc sql;
select a.customer_number,
b.tran_amt,
b.tran_date,
b.tran_code
from tblCustomer_New a
left join tblTrans_All b
on a.customer_number=b.Customer_number
where btran_date > '15JUN2006'd;
quit;

I just get the customers who had A transaction... thing is I want them
all, thus the lft join.

What happens is the where statement transposes the process into an inner
join.

Is there some way I can get round this???

Thanks

Andrew
From: Andreww on 14 Sep 2006 05:31
Hi Folks - this worked well... so thanks!

from tblCustomer_New a
left join tblTrans_All b
on ((a.customer_number=b.Customer_number) and (b.tran_date >
'15JUN2006'd));


Andrew


Sigurd Hermansen wrote:
> Andrew:
> ... left join (select * from tblTrans_All where tran_date >
> '15JUN2006'd) as b ....
>
> takes the guesswork out and works efficiently as well.
> Sig
>
> -----Original Message-----
> From: owner-sas-l(a)listserv.uga.edu [mailto:owner-sas-l(a)listserv.uga.edu]
> On Behalf Of Andreww
> Sent: Tuesday, September 12, 2006 12:07 PM
> To: sas-l(a)uga.edu
> Subject: proc sql & left join & where statement
>
>
> I have a table tblCustomers_new and transactions tblTrans_all
>
> What I want to do is pull out all my new customers (tblCustomers_New)
> trans.... but only where trans_date > '15JUN2006'
>
> If I do:
>
> proc sql;
> select a.customer_number,
> b.tran_amt,
> b.tran_date,
> b.tran_code
> from tblCustomer_New a
> left join tblTrans_All b
> on a.customer_number=b.Customer_number
> where btran_date > '15JUN2006'd;
> quit;
>
> I just get the customers who had A transaction... thing is I want them
> all, thus the lft join.
>
> What happens is the where statement transposes the process into an inner
> join.
>
> Is there some way I can get round this???
>
> Thanks
>
> Andrew

First  |  Prev  | 
Pages: 1 2
Prev: SAS-to-Excel Oddity II
Next: How to get Individual Region value from the macro list ???????