topic Using Except Operator with a Subquery in SAS Procedures

Using Except Operator with a Subquery

InfoAlisaA — Mon, 27 Feb 2012 21:40:46 GMT

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

Using Except Operator with a Subquery

Hima — Mon, 27 Feb 2012 21:50:48 GMT

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;

Using Except Operator with a Subquery

InfoAlisaA — Mon, 27 Feb 2012 21:56:35 GMT

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

Using Except Operator with a Subquery

Hima — Mon, 27 Feb 2012 22:02:29 GMT

Do you have employee name in this table orion.Order_fact?

Using Except Operator with a Subquery

InfoAlisaA — Mon, 27 Feb 2012 22:03:54 GMT

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

Using Except Operator with a Subquery

art297 — Mon, 27 Feb 2012 22:10:38 GMT

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?

Using Except Operator with a Subquery

InfoAlisaA — Mon, 27 Feb 2012 22:13:19 GMT

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.

Using Except Operator with a Subquery

Hima — Mon, 27 Feb 2012 22:13:52 GMT

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;

Using Except Operator with a Subquery

InfoAlisaA — Mon, 27 Feb 2012 22:34:35 GMT

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;

Using Except Operator with a Subquery

InfoAlisaA — Mon, 27 Feb 2012 22:38:16 GMT

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