The SQL Procedure

Example 12: Joining Two Tables and Calculating a New Value

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 6 1
store2 5 2
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
;

Input Tables

STORES and HOUSES

The tables contain X and Y coordinates that represent the location of the stores and houses.

Program

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);

Output

Chapter Contents
Previous
Next
Top of Page