Help using Base SAS procedures

Using Except Operator with a Subquery

Reply
Frequent Contributor
Posts: 90

Using Except Operator with a Subquery

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

Regular Contributor
Posts: 233

Using Except Operator with a Subquery

Posted in reply to InfoAlisaA

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;

Frequent Contributor
Posts: 90

Using Except Operator with a Subquery

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

Regular Contributor
Posts: 233

Using Except Operator with a Subquery

Posted in reply to InfoAlisaA

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

Frequent Contributor
Posts: 90

Using Except Operator with a Subquery

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

PROC Star
Posts: 7,474

Using Except Operator with a Subquery

Posted in reply to InfoAlisaA

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?

Frequent Contributor
Posts: 90

Using Except Operator with a Subquery

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.

Regular Contributor
Posts: 233

Using Except Operator with a Subquery

Posted in reply to InfoAlisaA

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;

Frequent Contributor
Posts: 90

Using Except Operator with a Subquery

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;

Frequent Contributor
Posts: 90

Using Except Operator with a Subquery

Posted in reply to InfoAlisaA

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! Smiley Happy

Alisa

Ask a Question
Discussion stats
  • 9 replies
  • 2166 views
  • 6 likes
  • 3 in conversation