Introduction

Suppose you are generating a report based on data from a health clinic. You want to display the results of individual patient stress tests taken in 1998 (which are stored in table A), followed by the results from stress tests taken in 1999 (which are stored in table B). Instead of combining the table rows horizontally, as you would in a PROC SQL join, you want to combine the table rows vertically (one on top of the other).

Diagram of two tables combined vertically

When you need to select data from multiple tables and combine the tables vertically, PROC SQL can be an efficient alternative to using other SAS procedures or the DATA step. In a PROC SQL set operation, you use one of four set operators (EXCEPT, INTERSECT, UNION, and OUTER UNION) to combine tables (and views) vertically by combining the results of two queries:

     proc sql;
        select *
           from a
        set-operator
        select *
           from b;

Each set operator combines the query results in a different way.

In this lesson, you will learn how to use the various set operators, with or without the optional keywords ALL and CORR (CORRESPONDING), to combine the results of multiple queries.

Note In this lesson, the references to tables are also applicable to views, unless otherwise noted.

 

1.5 hours



In this lesson, you learn to

  • combine the results of multiple PROC SQL queries in different ways by using the set operators EXCEPT, INTERSECT, UNION, and OUTER UNION
  • modify the results of a PROC SQL set operation by using the keywords ALL and CORR (CORRESPONDING)
  • compare PROC SQL outer unions with other SAS techniques.

complete the following lessons:

  • .