DATA Step, Macro, Functions and more

How to merge many to many

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

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
AAD
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

 

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

 


Accepted Solutions
Solution
‎04-06-2017 10:10 AM
Super User
Posts: 19,815

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 

View solution in original post


All Replies
Solution
‎04-06-2017 10:10 AM
Super User
Posts: 19,815

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 137 views
  • 1 like
  • 3 in conversation