BookmarkSubscribeRSS Feed
InfoAlisaA
Calcite | Level 5

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

9 REPLIES 9
Hima
Obsidian | Level 7

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;

InfoAlisaA
Calcite | Level 5

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

Hima
Obsidian | Level 7

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

InfoAlisaA
Calcite | Level 5

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

art297
Opal | Level 21

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?

InfoAlisaA
Calcite | Level 5

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.

Hima
Obsidian | Level 7

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;

InfoAlisaA
Calcite | Level 5

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;

InfoAlisaA
Calcite | Level 5

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

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!

What is Bayesian Analysis?

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.

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
  • 9 replies
  • 6035 views
  • 6 likes
  • 3 in conversation