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;
This is not an answer to your question, but I'd suggest you consider the inefficiency of a where clause like
WHERE Year(datepart(dateord))=2016;
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.
regards,
Mark
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 ;
select b.buyers,b.f_name,b.l_name
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;
quit;
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;
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.
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;
Great! Post the queries and we might be able to suggest optimisations.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.