SAS PROC SQL Generating a Cartesian Product

Question

I am learning PROC SQL and wonder why following two result are different:

I have two data set as following

data data1;
    input name $ value;
    datalines;
a 1
b 3
c 5
;
run;

data data2;
    input name $ value;
    datalines;
A 2
B 4
C 6
D 8
;
run;

1, Method ONE

proc sql;
    select * 
    from data1, data2
    ;
quit;

2, Method TWO

proc sql noprint;
    create table output as
    select * from data1, data2
    ;
quit;

1 is just generating an result, 2 is writing generated result in a work table. The query result should be the same, can you explain what the difference is, that you get? — kl78, Sep 07 '15 at 09:06

score 1 · Accepted Answer · answered Sep 07 '15 at 09:23

In method 1 you are just displaying the data. So no issues.

However in method 2, you are trying to create a table "output". Did you notice the variable names in dataset "data2"? It is name and value. These variables are already present in data1.

You will get the warning as below:

WARNING: Variable name already exists on file WORK.OUTPUT.

WARNING: Variable value already exists on file WORK.OUTPUT.

Just rename the variables in one of the dataset and try executing the code. You will get same results.

SAS PROC SQL Generating a Cartesian Product

1 Answers1