Sample 25270: Using PROC SQL to generate the Cartesian Product
When joining multiple tables, the default behavior of PROC SQL is to
build all possible combinations between the tables. This is also
known as the Cartesian Product.
The following note will be written to the SAS log when a Cartesian Product is created:
NOTE: The execution of this query involves performing one or
more Cartesian product joins that can not be optimized.
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
data one;
input id $ fruit $;
datalines;
a apple
a apple
b banana
c coconut
c coconut
c coconut
;
data two;
input id $ color $;
datalines;
a amber
b brown
b black
c cocoa
c cream
;
proc sql;
create table three as
select one.*
,two.color
from one
,two;
quit;
proc print data=three;
run;
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
Obs id fruit color
1 a apple amber
2 a apple amber
3 b banana amber
4 c coconut amber
5 c coconut amber
6 c coconut amber
7 a apple brown
8 a apple brown
9 b banana brown
10 c coconut brown
11 c coconut brown
12 c coconut brown
13 a apple black
14 a apple black
15 b banana black
16 c coconut black
17 c coconut black
18 c coconut black
19 a apple cocoa
20 a apple cocoa
21 b banana cocoa
22 c coconut cocoa
23 c coconut cocoa
24 c coconut cocoa
25 a apple cream
26 a apple cream
27 b banana cream
28 c coconut cream
29 c coconut cream
30 c coconut cream
This example illustrates the default behavior of PROC SQL, which is to build all possible combinations between the tables. Also, known as the Cartesian Product.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL
|
| Date Modified: | 2005-03-19 03:02:44 |
| Date Created: | 2005-03-18 11:27:59 |
Operating System and Release Information
| SAS System | Base SAS | All | n/a | n/a |
