BookmarkSubscribeRSS Feed
afs
Calcite | Level 5 afs
Calcite | Level 5

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;

 

8 REPLIES 8
mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
mkeintz
PROC Star
And oh yes, I forgot. What errors are you seeing in your log?
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Patrick
Opal | Level 21

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;
afs
Calcite | Level 5 afs
Calcite | Level 5
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
Patrick
Opal | Level 21

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

 

 

PGStats
Opal | Level 21

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
afs
Calcite | Level 5 afs
Calcite | Level 5
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
PGStats
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1211 views
  • 3 likes
  • 4 in conversation