proc sql & left join & where statement
in [SAS]
|
From: Andreww on 12 Sep 2006 12:06 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: LouisBB on 12 Sep 2006 12:53 Dear Andreww, The left join generates internally a sort of missing values for all columns from b where b does not supply a record. Since this missing will never equal '15JUN2006'd your WHERE filters all those records away. Try moving the condition to your ON clause. from tblCustomer_New a left join tblTrans_All b on ((a.customer_number=b.Customer_number) and (b.tran_date > '15JUN2006'd)); LouisBB. "Andreww" <andrew.whittam(a)gmail.com> wrote in message news:1158077209.988076.211610(a)i42g2000cwa.googlegroups.com... >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: Gerhard Hellriegel on 12 Sep 2006 12:26 My SQL knowledge is not good enough to figure out, what's possible there, but one question: if a customer has no transaction, what should be the trans_date of that non-available transaction?? In my opinion, that must be a missing value, right? In that case a WHERE clause which returns only customers with b.tran_date (by the way: btran_date like in your SQL code should return nothing!). Perhaps a ...or b.tran_date=. could help? On Tue, 12 Sep 2006 09:06:50 -0700, Andreww <andrew.whittam(a)GMAIL.COM> wrote: >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: "Terjeson, Mark" on 12 Sep 2006 12:48 Hi Andrew, If you add a period in the where clause on the table alias, do you get more desired results? i.e. where b.tran_date > '15JUN2006'd; vs. where btran_date > '15JUN2006'd; Hope this is helpful. Mark Terjeson Senior Programmer Analyst, IM&R Russell Investment Group Russell Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Andreww Sent: Tuesday, September 12, 2006 9:07 AM To: SAS-L(a)LISTSERV.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 12 Sep 2006 14:24 Hi - thanks for such speedy replies. Mark - would be great if it worked... but it was a typo... I did actually use b.tran_date cf btran_date. Gerhard - ... am still thinking about that one Loius - I'll try out tomorrow at work and let you know. Cheers all. Andrew "Terjeson, Mark" wrote: > Hi Andrew, > > If you add a period in the where clause > on the table alias, do you get more > desired results? > > i.e. > where b.tran_date > '15JUN2006'd; > vs. > where btran_date > '15JUN2006'd; > > > > > > Hope this is helpful. > > > Mark Terjeson > Senior Programmer Analyst, IM&R > Russell Investment Group > > > Russell > Global Leaders in Multi-Manager Investing > > > > > > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of > Andreww > Sent: Tuesday, September 12, 2006 9:07 AM > To: SAS-L(a)LISTSERV.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
|
Next
|
Last
Pages: 1 2 Prev: SAS-to-Excel Oddity II Next: How to get Individual Region value from the macro list ??????? |