11-19-2016 06:57 PM - last edited on 11-20-2016 12:32 AM by Reeza
I am getting errors in log which I cannot clear. Hope somebody can help me to check my below SQL code can be changed to make my code work.I want to create through 2 SQL queries which should display the buyer id and the f_name and l_names of all the buyers who made no accessories orders in the year 2016. First SQL statement to create a dataset called accessories2016 which has all the columns in sasdata.accessories and the rows for which the year sold is 2016. Second SQL statement will be then used an outer join to complete my requirements.My code looks like this: I have two tables buyers and orders
proc sql; create table accessories2016 AS select * from sasdata.accessories WHERE Year(datepart(dateord))=2016; quit; proc sql ; select b.buyers,b.f_name,b.l_name from sasdata.buyers b left join sasdata.orders o on b.buyerid=b.buyerid where o.orderno is missing; quit;
11-19-2016 07:19 PM
This is not an answer to your question, but I'd suggest you consider the inefficiency of a where clause like
which will perform two functions on variable DATEORD for every observation.
Since dateord is apparently a datetime value, consider using datetime literals as upper and lower bounds on dateord, i.e.
where dateord between '01jan2016 00:00:00'dt and '31dec2016 23:59:59'dt;
The benefit is that you are comparing dateord to two fixed values - avoiding unnecessary computation. Possibly beneficial for large datasets.
11-19-2016 07:25 PM - edited 11-19-2016 07:42 PM
What's the Error? Please post the relevant section of the SAS log.
In below code shouldn't this be b.buyerid=o.buyerid
proc sql ;
from sasdata.buyers b
left join sasdata.orderIf you want all rows in table buyers with no match to table Orderno then use an EXIST clause.s o on b.buyerid=b.buyerid
where o.orderno is missing;
You write "Second SQL statement will be then used an outer join.." but in the code you've posted you're using a LEFT join.
I also can't see how this would achieve your goal as you then only keep variables from table Buyers. What will happen is that you end up with duplicate rows if there is a 1:M join.
From what you describe I believe you're after code logic as below (not tested)
proc sql; create table accessories2016 as select distinct buyerid from sasdata.accessories WHERE dateord between '01jan2016 00:00:00'dt and '31dec2016 23:59:59'dt ; quit; proc sql; select b.buyers,b.f_name,b.l_name from sasdata.buyers b where b.buyerid not in (select buyerid from accessories2016) ; quit;
11-19-2016 10:23 PM
11-20-2016 05:08 PM
If you're asking for actual and tested code then you need to post sample data (a SAS data step creating such data), explain what you want to do and post/describe the desired output.
11-20-2016 04:32 PM
You could use a set operation to get the list of desired buyerId's and a join to get other fields from the sasdata.buyers table
proc sql; create table nonBuyersId2016 as select buyerId from sasdata.buyers except select buyerId from sasdata.accessories where dateord between '01jan2016:00:00'dt and '31dec2016:23:59:59'dt; create table nonBuyers2016 as select a.buyerId, a.f_name, a.l_name from sasdata.buyers as a natural join nonBuyersId2016; quit;
11-24-2016 05:06 PM