Procedure features: |
CASE
expression |
joined-table component |
Cross join |
SELECT clause
|
|
Tables: |
PROCLIB.MARCH, FLIGHTS
|
This example joins a table with itself to get all the possible combinations
of the values in a column.
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
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
libname proclib 'SAS-library'; |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
options nodate pageno=1 linesize=80 pagesize=60; |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
proc sql;
create table flights as
select distinct dest
from proclib.march; |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
title 'Cities Serviced by the Airline'; |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
select * from flights; |
Cities Serviced by the Airline 1
Dest
----
FRA
LAX
LON
ORD
PAR
WAS
YYZ
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
title 'All Possible Connections'; |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
select f1.Dest, case
when f1.dest ne ' ' then 'to and from'
end,
f2.Dest |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
from flights as f1, flights as f2 |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
where f1.dest < f2.dest |
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
order by f1.dest; |
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
![Note about code](../../../../common/63294/HTML/default/images/snbut.gif) |
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; |
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
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.