Many to One Merge

[alexgouv]

Hi,

I have 2 datasets that do not share any variables. One has 100(cats) observations and the other has 1000(dogs) observations.

I would like to create a new dataset where each of the 1000 dogs gets all of the data from each of the 100 cats appended onto it.

The final dataset would have 100000 observations and would look like this:

     1. Dog1 <variables from dog1> <variables from cat1>

     2. Dog1 <variables from dog1> <variables from cat2>

     3. Dog1 <variables from dog1> <variables from cat3>

     ...

 101. Dog2 <variables from dog2> <variables from cat1>

     ...

Any help would be appreciated!

[Reeza]

This is known as a cartesion product or cross join.

Easiest way is via SQL, but your variables should have unique names.

proc sql;
create table want as
select *
from table1, table2;
quit;

[tsap · Accepted solution]

I generated two tables with some Dummy data:

DATA WORK.Have_Dog;
FORMAT 		Dog_Breed $9.  Dog_Gender $6. Dog_Name $12.;
INFORMAT	Dog_Breed $9.  Dog_Gender $6. Dog_Name $12.;
INPUT  		Dog_Breed      Dog_Gender      Dog_Name;
DATALINES;
Retriever Male Dog_Retr
Labrador Female	Dog_Labr
Poodle Male	Dog_Pood
Husky Female Dog_Husk
Mutt Male Dog_Mutt
;


DATA WORK.Have_Cat;
FORMAT 		Cat_Breed $9.  Cat_Gender $6. Cat_Name $12.;
INFORMAT	Cat_Breed $9.  Cat_Gender $6. Cat_Name $12.;
INPUT  		Cat_Breed      Cat_Gender     Cat_Name;
DATALINES;
Siamese Male Cat_Siam
Persian Female	Cat_Pers
Savannah Male	Cat_Sava
Himalayan Female Cat_Hima
Mix Male Cat_Mix
;

And this is the logic to perform the task you were requesting:

PROC SQL;
CREATE TABLE WORK.WANT_AllCombos	AS
	SELECT *
	FROM WORK.Have_Dog, WORK.HAve_Cat;
QUIT;

This is what the results of the dummy data going through this logic to join the data in the two tables:

Dog_Breed	Dog_Gender	Dog_Name	Cat_Breed	Cat_Gender	Cat_Name
Retriever	Male	        Dog_Retr	Siamese	        Male	        Cat_Siam
Retriever	Male	        Dog_Retr	Persian	        Female	        Cat_Pers
Retriever	Male	        Dog_Retr	Savannah	Male	        Cat_Sava
Retriever	Male	        Dog_Retr	Himalayan	Female	        Cat_Hima
Retriever	Male	        Dog_Retr	Mix	        Male	        Cat_Mix
Labrador	Female	        Dog_Labr	Siamese	        Male	        Cat_Siam
Labrador	Female	        Dog_Labr	Persian	        Female	        Cat_Pers
Labrador	Female	        Dog_Labr	Savannah	Male	        Cat_Sava
Labrador	Female	        Dog_Labr	Himalayan	Female	        Cat_Hima
Labrador	Female	        Dog_Labr	Mix	        Male	        Cat_Mix
Poodle	        Male	        Dog_Pood	Siamese         Male	        Cat_Siam
Poodle	        Male	        Dog_Pood	Persian	        Female	        Cat_Pers
Poodle	        Male	        Dog_Pood	Savannah	Male	        Cat_Sava
Poodle	        Male	        Dog_Pood	Himalayan	Female	        Cat_Hima
Poodle	        Male	        Dog_Pood	Mix	        Male	        Cat_Mix
Husky	        Female	        Dog_Husk	Siamese	        Male	        Cat_Siam
Husky	        Female	        Dog_Husk	Persian	        Female	        Cat_Pers
Husky	        Female	        Dog_Husk	Savannah	Male	        Cat_Sava
Husky	        Female	        Dog_Husk	Himalayan	Female	        Cat_Hima
Husky	        Female	        Dog_Husk	Mix	        Male	        Cat_Mix
Mutt	        Male	        Dog_Mutt	Siamese	        Male	        Cat_Siam
Mutt	        Male	        Dog_Mutt	Persian	        Female	        Cat_Pers
Mutt	        Male	        Dog_Mutt	Savannah	Male	        Cat_Sava
Mutt	        Male	        Dog_Mutt	Himalayan	Female	        Cat_Hima
Mutt	        Male	        Dog_Mutt	Mix	        Male	        Cat_Mix