Example: STAR JOIN with two lookup table files

In this example, the case data file contains numeric codes for Field1 and Field2, and descriptive values for each of those fields are contained in two separate lookup tables.

Data

Table 1. Case data file: fact.sav
Name1	Name2	Field1	Field2	Field3
Barney	Jones	1	1	10/29/1987
Fred	Smith	1	3	10/28/1986
Barney	Jones	2	2	12/1/2001
Joe	Smith	5	4	12/1/2011
Nancy	Jones	99

Table 2. Lookup table: field1_values.sav
Field1	Value1
1	One
2	Two
3	Three
99	Missing

Table 3. Lookup table: field2_values.sav
Field2	Value2
1	A
2	B
3	C
5	E

Syntax

STAR JOIN
  /SELECT t1.Name1, t1.Name2, t1.Field1, t1.Field2, t1.Field3, 
   t2.Value1, t3.Value2
  /FROM '/myfiles/fact.sav' AS t1 
  /JOIN '/myfiles/field1_values.sav' AS t2 
   ON t1.Field1 = t2.Field1
  /JOIN 'myfiles/field2_values.sav' AS t3 
   ON t1.Field2 = t3.Field2
  /OUTFILE FILE='/myfiles/merged_data.sav'.

Result

Table 4. Merged files
Name1	Name2	Field1	Field2	Field3	Value1	Value2
Barney	Jones	1	1	10/29/1987	One	A
Fred	Smith	1	3	10/28/1986	One	C
Barney	Jones	2	2	12/1/2001	Two	B
Joe	Smith	5	4	12/1/2011	(blank)	(blank)
Nancy	Jones	99	(sysmis)	(sysmis)	Missing

All records from the case data file are included; so the fourth record (Joe Smith) is included even though the Field1 value of 5 and the Field2 value of 4 are not present in either lookup table.
Only records from the lookup tables that have matching keys in the case data file are included; so the Field1 record with a value of 3 in field1_values.sav and the Field2 record with a value of 5 in field2_values.sav are not included.
In this example, since the SELECT statement includes all fields in all three tables other than those specified as key fields with ON, an asterisk (*) instead of the explicit list of fields would yield the same result.