Hello Everyone,
I am trying to use an Except operator with a subquery for one of my homework problems.
What I need to do is to display all of the Sales Employees who did not make any sales in 2007.
orion.Sales has Employee_ID
orion.Order_fact has Employee_ID and Order_date
orion.Employee_Addresses has Employee_ID and Employee_Name
Here is my code so far:
proc sql;
select distinct s.Employee_ID, a.Employee_Name
from orion.Sales as s,
orion.Employee_Addresses as a
where s.Employee_ID=a.Employee_ID
except all
select distinct s.Employee_ID
from orion.Sales as s,
orion.Order_fact as of
where year(of.Order_date) ne 2007 and
s.Employee_ID=of.Employee_ID
order by Employee_Name;
quit;
This does not produce the results I need where the 2007 records are filtered out.
I also get this message when I look at the log:
WARNING: A table has been extended with null columns to perform the EXCEPT ALL set operation.
So I know I am doing something wrong, but I am not sure what.
If anyone can take a look at my code and help me with this, it would be greatly appreciated.
Thanks!
Alisa
To perform Except function the columns must have to be same. In your query you have only employee ID but no employee name.
Correct syntax:
proc sql;
select Employee_ID, Employee_Name from table_a
except
select Employee_ID, Employee_Name from table_b;
quit;
Try running the below code. It should work.
proc sql;
select distinct s.Employee_ID, a.Employee_Name
from orion.Sales as s inner join orion.Employee_Addresses as a on s.Employee_ID=a.Employee_ID
inner join orion.Order_fact as of on s.Employee_ID=of.Employee_ID
where year(of.Order_date) ne 2007 order by Employee_Name;
quit;
Hi Hima,
This code does work, but it does not satisfy the exercise that I am trying to do.
I have to use except in my code for my exercise even though I know that I have things that do not match.
Thanks,
Alisa
Do you have employee name in this table orion.Order_fact?
Unfortunately, I don't.
Here is the proc contents for orion.Order_fact:
Data Set Name ORION.ORDER_FACT Observations 617
# Variable Type Len Format Label
11 CostPrice_Per_Unit Num 8 DOLLAR13.2 Cost Price Per Unit
1 Customer_ID Num 8 12. Customer ID
5 Delivery_Date Num 8 DATE9. Date Order was Delivered
12 Discount Num 8 PERCENT. Discount in percent of Normal Total Retail Price
2 Employee_ID Num 8 12. Employee ID
4 Order_Date Num 8 DATE9. Date Order was placed by Customer
6 Order_ID Num 8 12. Order ID
7 Order_Type Num 8 Order Type
8 Product_ID Num 8 12. Product ID
9 Quantity Num 8 Quantity Ordered
3 Street_ID Num 8 12. Street ID
10 Total_Retail_Price Num 8 DOLLAR13.2 Total Retail Price for This Product
So why don't you just modify Hima's code to do the first part only selecting EmployeeID, and then joining the result with the other file that contains all of the info you need?
Hi Art,
I am not sure what you mean. Hima's code doesn't contain any except set operators that I need for this problem.
How about this?
proc sql;
create table test as
select distinct s.Employee_ID, a.Employee_Name from orion.Sales as s inner join orion.Employee_Addresses as a on s.Employee_ID=a.Employee_ID
inner join orion.Order_fact as of on s.Employee_ID=of.Employee_ID
where year(of.Order_date) ne 2007 order by Employee_Name;
quit;
proc sql;
select distinct s.Employee_ID, a.Employee_Name from orion.Sales as s inner join orion.Employee_Addresses as a on s.Employee_ID=a.Employee_ID
except
select distinct s.Employee_ID, a.Employee_Name from test;
quit;
When I try this code:
proc sql;
select distinct s.Employee_ID, a.Employee_Name
from orion.Sales as s
inner join orion.Employee_Addresses as a on s.Employee_ID=a.Employee_ID
except
select distinct s.Employee_ID, a.Employee_Name from test;
quit;
This is what I get:
3235 proc sql;
3236 select distinct s.Employee_ID, a.Employee_Name
3237 from orion.Sales as s
3238 inner join orion.Employee_Addresses as a on s.Employee_ID=a.Employee_ID
3239 except
3240 select distinct s.Employee_ID, a.Employee_Name from test;
ERROR: Unresolved reference to table/correlation name s.
ERROR: Unresolved reference to table/correlation name a.
3241 quit;
Hi Hima,
I finally got my code to work.
Here is what I got:
proc sql;
select distinct s.Employee_ID, a.Employee_Name
from orion.Sales as s,
orion.Employee_Addresses as a
where s.Employee_ID=a.Employee_ID
except corr
select distinct of.Employee_ID, a.Employee_Name
from orion.Employee_Addresses as a,
orion.Order_fact as of
where year(of.Order_date) = 2007
order by Employee_Name;
quit;
Thanks for all of your help though!
Alisa
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.