topic How to merge many to many in SAS Programming

How to merge many to many

brophymj — Thu, 06 Apr 2017 13:43:25 GMT

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

Reeza — Thu, 06 Apr 2017 13:47:40 GMT

Use a SQL CROSS JOIN.

SELECT a., b.*

from have as a, have2 as b

Select a.*, b.*

from have as a

cross join have2 as b

Mokben — Thu, 06 Apr 2017 14:05:18 GMT

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() ;