Sentry Page Protection
Proc SQL II [7-17]
Combine Tables Vertically
Similar to the SET statement in the data step, the SET operator in Proc SQL allows you to combine tables vertically, stacking the tables one on top of another.
There are four types of SET operators in Proc SQL:
Let's look at some examples.
Copy and run the code from the yellow box below:
There are four types of SET operators in Proc SQL:
- Union
- Except
- Intersect
- Outer Union
Let's look at some examples.
Copy and run the code from the yellow box below:
The CUST1 and CUST2 tables each contain a list of three customers.
Note: Customer Sam appears in both tables.
CUST1
CUST2
Union Set Operator
The Union Set operator concatenates the two input tables.
It includes all of the rows from each table. However, it also removes the duplicate rows.
Let's look at an example below:
Proc sql;
select * from cust1
union
select * from cust2;
quit;
select * from cust1
union
select * from cust2;
quit;
The Union operator in this example does the following:
- It concatenates the two tables (i.e. CUST1 and CUST2).
- It sorts the concatenated table.
- It removes the duplicate rows.
Note: there is only one row for customer Sam, even though the customer appears in both input tables.
Except Set Operator
The Except Set operator also concatenates the two input tables.
However, it includes only the rows from the first table that are not in the second table.
Let's look at an example.
Proc sql;
select * from cust1
except
select * from cust2;
quit;
select * from cust1
except
select * from cust2;
quit;
The Except operator includes only the rows in the CUST1 table that are not in the CUST2 table.
Only two rows of data are included in the output:
Intersect Set Operator
The Intersect Set operator is similar to the Union and Except operators.
It also concatenates the input tables. However, it includes only the rows that appear in both input tables.
Below is an example.
Proc sql;
select * from cust1
intersect
select * from cust2;
quit;
select * from cust1
intersect
select * from cust2;
quit;
Customer Sam is the only customer who appears in both tables.
It is included in the output table:
In the next few sections, we will look at how the SET operators deal with duplicate rows and overlaying columns.
Let's look at one more example.
Copy and run the code from the yellow box below:
The code above creates two tables: DEPART1 and DEPART2.
Each table contains a list of employees and their job levels (i.e. staff vs manager).
DEPART1
DEPART2
We'd like to have the list of managers from both departments.
We can use the Union Set operator to create such a list.
Example
proc sql;
select * from depart1 where JobLevel = "Manager"
union
select * from depart2 where JobLevel = "Manager";
quit;
select * from depart1 where JobLevel = "Manager"
union
select * from depart2 where JobLevel = "Manager";
quit;
The output table contains the combined list of managers from each department:
Exercise
Copy and run the code from the yellow box below:
Copy and run the code from the yellow box below:
The code above creates the STORE1 and STORE2 tables.
Each table contains a list of products and the date the stock arrived.
You are the inventory manager and you'd like to look at the list of products that have arrived more than 90 days ago, and still haven't sold out.
Assuming today's date is January 12, 2015, write a Proc SQL step to put together the list of products that are more than 90 days old from both stores.
Need some help?
HINT:
The numeric representation of the date January 12, 2015 is '12JAN2015'd.
SOLUTION:
proc sql;
select * from store1 where '12JAN2015'd - stockdate > 90
union
select * from store2 where '12JAN2015'd - stockdate > 90
;
quit;
Fill out my online form.