merge by two identifiers

Hi,

I need to merge two matrices based on two identifiers: id and date. The original data I have is as follow:

data1
 id   date v1
3948  2000 99
3948  2001 84
2456  1997 74
2456  1998 89
2456  1999 48
2456  2000 43
2456  2001 45
2456  2002 49
5555  2000 99

data2
 id   date v2
3948  2000 98
2456  1999 28
2456  2000 23
5555  2000 54
6666  1997 88
7777  1998 56

And I need to change the original data into the matrix as below;

merged
id   date  v1 v2
3948  2000 99 98
3948  2001 84  .
2456  1997 74  .
2456  1998 89  .
2456  1999 48 28
2456  2000 43 23
2456  2001 45  .
2456  2002 49  .
5555  2000 99 54
6666  1997  . 88

I could easily do this in SAS by 'proc sort' and 'merge by' command but I can't find how to do this in matlab. I struggled with join function, but I couldn't resolve it when we have two identifiers. Can anyone help me to solve this problem? Thank you.

Minsoo

Answer 1

[Paulo Silva]

m1=[3948  2000 99
3948  2001 84
2456  1997 74
2456  1998 89
2456  1999 48
2456  2000 43
2456  2001 45
2456  2002 49
5555  2000 99]

m2=[3948  2000 98
2456  1999 28
2456  2000 23
5555  2000 54
6666  1997 88
7777  1998 56]

m3=[3948  2000 99 98
3948  2001 84  nan
2456  1997 74  nan
2456  1998 89  nan
2456  1999 48 28
2456  2000 43 23
2456  2001 45  nan
2456  2002 49  nan
5555  2000 99 54
6666  1997  nan 88]

m4=nan(size(m1,1)+size(m2,1),4);
[c, ia, ib] = intersect(m1(:,1:2), m2(:,1:2),'rows');
m4(1:numel(ia),1:4)=[m1(ia,:) m2(ib,end)];
m1(ia,:)=[];m2(ib,:)=[];
m4((numel(ia)+1):(size(m1,1)+numel(ia)),1:3)=m1;
m4((numel(ib)+numel(ia)+2):(1+size(m2,1)+numel(ia)+numel(ib)),1:2)=m2(:,1:2);
m4((numel(ib)+numel(ia)+2):(1+size(m2,1)+numel(ia)+numel(ib)),4)=m2(:,end);
f=find(isnan(m4(:,1)));
m4(f(1):end,:)=[];
m4

Answer 2

[Walter Roberson]

d = [data1;data2];
M = [d(:,1) * 10000 + d(:,2), d(:,3), d(:,4)];

Now apply the merge from your previous question.

Then split column 1 of the result in to two columns, floor(OutMat(:,1) ./ 10000), and mod(OutMat(:,1), 10000)

No need to invent new merging techniques ;-)