SQL Procedure

Example 13: Producing All the Possible Combinations of the Values in a Column

Features:

CASE expression

joined-table component

Cross join

SELECT clause : DISTINCT keyword

Table names:

PROCLIB.MARCH

FLIGHTS

Details

This example joins a table with itself to get all the possible combinations of the values in a column.

proc sql outobs=10;
   title 'PROCLIB.MARCH';
   title2 'First 10 Rows Only';
   select * from proclib.march;

title;

PROCLIB.MARCH

Program to Create the Flights Table

libname proclib 'SAS-library';

proc sql;
   create table flights as
      select distinct dest
         from proclib.march;

title 'Cities Serviced by the Airline';

select * from flights;

Program Description

Declare the PROCLIB library.The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

Create the FLIGHTS table.The CREATE TABLE statement creates the table FLIGHTS from the output of the query. The SELECT clause selects the unique values of Dest. DISTINCT specifies that only one row for each value of city be returned by the query and stored in the table FLIGHTS. The FROM clause specifies PROCLIB.MARCH as the table to select from.

proc sql;
   create table flights as
      select distinct dest
         from proclib.march;

Specify the title.

title 'Cities Serviced by the Airline';

Display the entire FLIGHTS table.

select * from flights;

Output for Flights Table

Cities Serviced by the Airline

Program Using Conventional Join

proc sql;

title 'All Possible Connections';

select f1.Dest, case
                      when f1.dest ne ' ' then 'to and from'
                   end,
          f2.Dest

from flights as f1, flights as f2

where f1.dest < f2.dest

order by f1.dest;

Program Description

proc sql;

Specify the title.

title 'All Possible Connections';

Select the columns.The SELECT clause specifies three columns for the output. The prefixes on DEST are table aliases to specify which table to take the values of Dest from. The CASE expression creates a column that contains the character string to and from.

select f1.Dest, case
                      when f1.dest ne ' ' then 'to and from'
                   end,
          f2.Dest

Specify the type of join.The FROM clause joins FLIGHTS with itself and creates a table that contains every possible combination of rows (a Cartesian product). The table contains two rows for each possible route. For example, PAR <-> WAS and WAS <-> PAR.

from flights as f1, flights as f2

Specify the join criterion.The WHERE clause subsets the internal table by choosing only those rows where the name in F1.Dest sorts before the name in F2.Dest. Thus, there is only one row for each possible route.

where f1.dest < f2.dest

Sort the output.ORDER BY sorts the result by the values of F1.Dest.

order by f1.dest;

Output Using Conventional Join

All Possible Connections

Program Using Cross Join

/*  */
proc sql;
   title 'All Possible Connections';
   select f1.Dest, case
                      when f1.dest ne ' ' then 'to and from'
                   end,
       f2.Dest
       from flights as f1 cross join flights as f2
       where f1.dest < f2.dest
       order by f1.dest;

Program Description

Specify a cross join.Because a cross join is functionally the same as a Cartesian product join, the cross join syntax can be substituted for the conventional join syntax.

/*  */
proc sql;
   title 'All Possible Connections';
   select f1.Dest, case
                      when f1.dest ne ' ' then 'to and from'
                   end,
       f2.Dest
       from flights as f1 cross join flights as f2
       where f1.dest < f2.dest
       order by f1.dest;

Output Using Cross Join

All Possible Connections