How to combine non matching datasets horizontally in SAS/SQL?

Question

I work in SAS writing both SAS base and SQL statements.

My problem is, that I have two datasets I want to combine horizontally.

The one data set is called Code and have one variable: Code_value It has 55 observations, with no duplicate values.

The other data set is called Persons and have one variable: Person_id It has 167 unique person_id values.

I want to create a dataset where I join these data sets. - There are no matching values in the two datasets. I want to force the data sets together, so I have a data set with for each person_id, there is a row with every code_value.

So i have combinations with these value combinations:

Code1 Pid1

Code1 Pid2

Code1 Pid3

...

Code2 Pid1

Code2 Pid2

Code2 Pid3

... etc. Ending up with a data set with 2 variables and 9185 rows in total.

I have tried data step with merge and also tried to write a sql with a full join, but with no luck.

Can anyone help?

Kind Regards Maria

Answer 1

This is known as a cross join. I prefer to explicitly list the cross join.

proc sql;
  create table want as
  select *
  from code
  CROSS JOIN
  persons;
quit;

Or without any specifications:

proc sql;
create table want as
select *
from one, two;

Both should give you the same answer.

Answer 2

The ON condition for the join should be 1=1. This will cause all rows in one to match all rows in two.

Example, 3 rows in one, 5 rows in two, 15 rows in crossings:

data one;
  do i = 1 to 3;
    output;
  end;
run;

data two;
  do j = 1 to 5;
    output;
  end;
run;

proc sql;
  create table crossings as
  select * 
  from one full join two on 1=1
  ;
quit;

If there are any column names in common you should either rename them or coalesce() them.