The SQL Procedure

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

Procedure features:

CASE expression

joined-table component

SELECT clause

DISTINCT keyword

Tables:

PROCLIB.MARCH, FLIGHTS

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

Input Table

PROCLIB.MARCH First 10 Rows Only Flight Date Depart Orig Dest Miles Boarded Capacity ----------------------------------------------------------------- 114 01MAR94 7:10 LGA LAX 2475 172 210 202 01MAR94 10:43 LGA ORD 740 151 210 219 01MAR94 9:31 LGA LON 3442 198 250 622 01MAR94 12:19 LGA FRA 3857 207 250 132 01MAR94 15:35 LGA YYZ 366 115 178 271 01MAR94 13:17 LGA PAR 3635 138 250 302 01MAR94 20:22 LGA WAS 229 105 180 114 02MAR94 7:10 LGA LAX 2475 119 210 202 02MAR94 10:43 LGA ORD 740 120 210 219 02MAR94 9:31 LGA LON 3442 147 250

Program

libname proclib 'SAS-data-library';

options nodate pageno=1 linesize=80 pagesize=60;

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

   title 'Cities Serviced by the Airline';

select * from flights;

Output

FLIGHTS Table

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;

Output

Chapter Contents
Previous
Next
Top of Page