DATA Step, Macro, Functions and more

Two queries to get result

Reply
Contributor afs
Contributor
Posts: 28

Two queries to get result

[ Edited ]

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;

 

Trusted Advisor
Posts: 1,022

Re: Two queries to get result

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

Trusted Advisor
Posts: 1,022

Re: Two queries to get result

And oh yes, I forgot. What errors are you seeing in your log?
Respected Advisor
Posts: 4,173

Re: Two queries to get result

[ Edited ]

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;
Contributor afs
Contributor
Posts: 28

Re: Two queries to get result

Dear Patric ,
b.buyerid=b.buyerid was a typo error .
For the second statement i wanted an outer join to complete my statement.As i was trying rather sub query to use outer join . If you can help
Respected Advisor
Posts: 4,173

Re: Two queries to get result

@afs

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.

 

 

Respected Advisor
Posts: 4,932

Re: Two queries to get result

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;
PG
Contributor afs
Contributor
Posts: 28

Re: Two queries to get result

I used the following and i got the table for 2012
WHERE Year(accessories.dateord)=2012;
and then i used the orderno is missing and got the
select a.buyerId, a.f_name, a.l_name to get the result.
Wanted your input
Respected Advisor
Posts: 4,932

Re: Two queries to get result

Great! Post the queries and we might be able to suggest optimisations.

PG
Ask a Question
Discussion stats
  • 8 replies
  • 324 views
  • 3 likes
  • 4 in conversation