The SQL Procedure

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

Procedure features:

CASE expression

joined-table component

Cross join

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                                1
                              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 to Create the Flights Table

libname proclib 'SAS-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: Listing

                         Cities Serviced by the Airline                        1

                                      Dest
                                      ----
                                      FRA 
                                      LAX 
                                      LON 
                                      ORD 
                                      PAR 
                                      WAS 
                                      YYZ

Program Using Conventional Join

   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: Listing

                            All Possible Connections                           2

                            Dest               Dest
                            -----------------------
                            FRA   to and from  LAX 
                            FRA   to and from  LON 
                            FRA   to and from  WAS 
                            FRA   to and from  ORD 
                            FRA   to and from  PAR 
                            FRA   to and from  YYZ 
                            LAX   to and from  LON 
                            LAX   to and from  PAR 
                            LAX   to and from  WAS 
                            LAX   to and from  ORD 
                            LAX   to and from  YYZ 
                            LON   to and from  ORD 
                            LON   to and from  WAS 
                            LON   to and from  PAR 
                            LON   to and from  YYZ 
                            ORD   to and from  WAS 
                            ORD   to and from  PAR 
                            ORD   to and from  YYZ 
                            PAR   to and from  WAS 
                            PAR   to and from  YYZ 
                            WAS   to and from  YYZ

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;

Output: Listing

                            All Possible Connections                           1

                            Dest               Dest
                            -----------------------
                            FRA   to and from  LAX 
                            FRA   to and from  LON 
                            FRA   to and from  WAS 
                            FRA   to and from  ORD 
                            FRA   to and from  PAR 
                            FRA   to and from  YYZ 
                            LAX   to and from  LON 
                            LAX   to and from  PAR 
                            LAX   to and from  WAS 
                            LAX   to and from  ORD 
                            LAX   to and from  YYZ 
                            LON   to and from  ORD 
                            LON   to and from  WAS 
                            LON   to and from  PAR 
                            LON   to and from  YYZ 
                            ORD   to and from  WAS 
                            ORD   to and from  PAR 
                            ORD   to and from  YYZ 
                            PAR   to and from  WAS 
                            PAR   to and from  YYZ 
                            WAS   to and from  YYZ

Top of Page