| Procedure features: |
|
GROUP BY
clause |
|
HAVING clause |
|
SELECT clause
|
ABS function |
|
FORMAT= column-modifier |
|
LABEL= column-modifier |
|
MIN summary
function |
|
** operator, exponentiation |
|
SQRT function | |
|
| Tables: |
STORES, HOUSES
|
This example joins two tables in order to compare and analyze values
that are unique to each table yet have a relationship with a column that is
common to both tables.
options ls=80 ps=60 nodate pageno=1 ;
data stores;
input Store $ x y;
datalines;
store1 5 1
store2 5 3
store3 3 5
store4 7 5
;
data houses;
input House $ x y;
datalines;
house1 1 1
house2 3 3
house3 2 3
house4 7 7
;
 |
STORES Table 1
Coordinates of Stores
Store x y
----------------------------
store1 5 1
store2 5 3
store3 3 5
store4 7 5 HOUSES Table 2
Coordinates of Houses
House x y
----------------------------
house1 1 1
house2 3 3
house3 2 3
house4 7 7
| |
| |
options nodate pageno=1 linesize=80 pagesize=60; |
| |
proc sql;
title 'Each House and the Closest Store';
select house, store label='Closest Store',
sqrt((abs(s.x-h.x)**2)+(abs(h.y-s.y)**2)) as dist
label='Distance' format=4.2
from stores s, houses h |
| |
group by house
having dist=min(dist); |
| |
Each House and the Closest Store 1
Closest
House Store Distance
----------------------------
house1 store1 4.00
house2 store2 2.00
house2 store3 2.00
house3 store3 2.24
house4 store4 2.00
| |
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.
