topic Re: Two queries to get result in SAS Programming

Two queries to get result

afs — Sun, 20 Nov 2016 05:32:53 GMT

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;

Re: Two queries to get result

mkeintz — Sun, 20 Nov 2016 00:19:10 GMT

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

Re: Two queries to get result

mkeintz — Sun, 20 Nov 2016 00:20:39 GMT

And oh yes, I forgot. What errors are you seeing in your log?

Re: Two queries to get result

Patrick — Sun, 20 Nov 2016 00:42:40 GMT

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;

Re: Two queries to get result

afs — Sun, 20 Nov 2016 03:23:25 GMT

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

Re: Two queries to get result

PGStats — Sun, 20 Nov 2016 21:32:12 GMT

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;

Re: Two queries to get result

Patrick — Sun, 20 Nov 2016 22:08:03 GMT

@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.

Re: Two queries to get result

afs — Thu, 24 Nov 2016 22:06:32 GMT

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

Re: Two queries to get result

PGStats — Thu, 24 Nov 2016 22:30:15 GMT

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