Multiple to multiple merge cannot be done using SAS data step (actually it can be done, but the result will be unexpected), so normally, when dealing with multiple to multiple merge, we will use sql, please see the detailed code in below:
data main;
input coma$ year qtr comb$ varm;
index1 = _N_; /*Used for ordering data in OutTable*/
cards;
阿里 2014 1 顺丰 1
阿里 2014 1 圆通 1.2
阿里 2014 2 圆通 2
阿里 2014 3 圆通 3
阿里 2015 1 圆通 4
阿里 2015 2 中通 5
京东 2014 1 顺丰 6
京东 2014 2 顺丰 7
京东 2014 3 顺丰 8
京东 2014 4 申通 9
京东 2015 1 申通 10
;
run;
data auxi;
input comb$ year qtr month vara varb;
index2 = _N_; /*Used for ordering data in OutTable*/
datalines;
顺丰 2013 4 12 1 2
顺丰 2014 1 1 2 3
顺丰 2014 1 2 3 4
顺丰 2014 1 3 4 5
顺丰 2014 2 4 5 6
顺丰 2014 2 5 6 7
顺丰 2014 2 6 7 8
顺丰 2014 3 7 8 9
顺丰 2014 3 8 9 10
顺丰 2014 3 9 10 11
顺丰 2014 4 10 11 12
顺丰 2014 4 11 12 13
顺丰 2014 4 12 13 14
圆通 2014 1 1 14 15
圆通 2014 1 2 15 16
圆通 2014 1 3 16 17
圆通 2014 2 4 17 18
圆通 2014 2 5 18 19
圆通 2014 2 6 19 20
圆通 2014 3 7 20 21
圆通 2014 3 8 21 22
圆通 2014 3 9 22 23
圆通 2014 4 10 23 24
圆通 2014 4 11 24 25
圆通 2014 4 12 25 26
圆通 2015 1 1 26 27
圆通 2015 1 2 27 28
圆通 2015 1 3 28 29
;
run;
proc sql;
create table OutTable(drop=index1) as
select main.*, auxi.vara, auxi.varb
from main, auxi
where main.comb = auxi.comb and main.year = auxi.year and main.qtr = auxi.qtr
order by main.index1, auxi.index2;
quit;
|