## How to merge many to many

Solved
Super Contributor
Posts: 261

# How to merge many to many

If you have two datasets:

data dataset1;
infile datalines;
input Date ;
datalines;
20150130
20150131
20150201
20150202
20150203
20150204
20150205
;
run;

data dataset2;
infile datalines;
input Symbol \$ ;
datalines;
AAA
AAB
AAC
AAE
AAF
AAG
;
run;

How would you combine them to get i.e. the resulting dataset will have N(dataset1 rows)*M(dataset2 rows) rows

 Date Symbol 20150130 AAA 20150131 AAA 20150201 AAA 20150202 AAA 20150203 AAA 20150204 AAA 20150205 AAA 20150130 AAB 20150131 AAB 20150201 AAB 20150202 AAB 20150203 AAB 20150204 AAB 20150205 AAB 20150130 AAC 20150131 AAC 20150201 AAC 20150202 AAC 20150203 AAC 20150204 AAC 20150205 AAC 20150130 AAD 20150131 AAD 20150201 AAD 20150202 AAD 20150203 AAD 20150204 AAD 20150205 AAD 20150130 AAE 20150131 AAE 20150201 AAE 20150202 AAE 20150203 AAE 20150204 AAE 20150205 AAE 20150130 AAF 20150131 AAF 20150201 AAF 20150202 AAF 20150203 AAF 20150204 AAF 20150205 AAF 20150130 AAF 20150131 AAF 20150201 AAF 20150202 AAF 20150203 AAF 20150204 AAF 20150205 AAF

Accepted Solutions
Solution
‎04-06-2017 10:10 AM
Super User
Posts: 23,661

## Re: How to merge many to many

Use a SQL CROSS JOIN.

SELECT a., b.*

from have as a, have2 as b

OR

Select a.*, b.*

from have as a

cross join have2 as b

All Replies
Solution
‎04-06-2017 10:10 AM
Super User
Posts: 23,661

## Re: How to merge many to many

Use a SQL CROSS JOIN.

SELECT a., b.*

from have as a, have2 as b

OR

Select a.*, b.*

from have as a

cross join have2 as b

New Contributor
Posts: 3

## Re: How to merge many to many

This work (Macro language with SAS ):

%Macro test_merge() ;
%Do i = 1 %to 7 ;
data data&i. ;
Set dataset2 ;
If _N_ = &i. ;
run ;

data data&i. ;
set data&i.
%do j = 2 %to 7 ;
data&i.
%end ; ;
run ;

Data total&i. ;
merge dataset1 data&i. ;
run ;
%End;

Data total ;
Set total: ;
run ;

%mend ;

%test_merge() ;

☑ This topic is solved.