topic Merge every observation in one dataset to each observation in another in SAS Programming

Merge every observation in one dataset to each observation in another

RoddyJ — Mon, 30 Nov 2020 17:28:21 GMT

I have two single column data sets with unique variables. I'd like to merge them to create a 2 column data set with an observation for every pair of variables from the the two original data sets. See example below.

I have the below two data sets with 1 column each:

cat1

cat2

I want the below data set:

cat1	cat2
a	1
a	2
a	3
a	4
b	1
b	2
b	3
b	4
c	1
c	2
etc...	etc...

Re: Merge every observation in one dataset to each observation in another

novinosrin — Mon, 30 Nov 2020 17:31:57 GMT


data one;
input cat1 $;
cards;
a
b
c
d
e
;

data two;
 input cat2 ;
 cards;
1
2
3
4
;

proc sql;
 create table want as
 select cat1, cat2
 from one , two
 order by cat1,cat2;
quit;

Re: Merge every observation in one dataset to each observation in another

Kurt_Bremser — Mon, 30 Nov 2020 17:32:24 GMT

Cartesian join in SQL:

proc sql;
create table want as
  select
    ds1.cat1,
    ds2.cat2
  from ds1, ds2
;
quit;