Many to Many merge issue 1

[6656]

Hi, does anyone have an idea to do many to many merge with MERGE statement intead of Proc Sql join.

Ex. Below two datasets to be merged by X.

data1 data2 Merged data should be

X Y X Z X Y Z
a 1 a m a 1 m
a 2 a n a 2 m
a 3 a 3 m
a 1 n
a 2 n
a 3 n

Thanks,
Mike

 

[Queryman]

* Example of many to many merge without using proc sql;

* file1 has 1 obs for id=1 and 2 obs for id=2;
data file1;
infile cards;
input id $ dateadm $ datesep $;
cards;
1 950101 950102
2 950101 950201
2 950210 950220
;

* file2 has 2 obs for id=1 and 3 obs for id=2;
data file2;
infile cards;
input id $ dateserv $;
cards;
1 950101
1 950105
2 950101
2 950110
2 950211
;

proc print data=file1;
title 'data=file1';
run;

proc print data=file2;
title 'data=file2';
run;

*make a new data set from file2 with one observation per id and a start
pointer and an end pointer which indicate the observation number of the
start and end for each id;

proc sort data=file2;
by id;
run;

data pointer;
set file2;
by id;
retain start end 0;
if first.id then start=_n_; *observation number of start for id;
if last.id then do;
end=_n_; * observation number of end for id;
output; * output 1 observation per id;
end;
keep id start end; * only keep the merge variable(s) and pointers;
run;

proc print data=pointer;
title 'Pointers to start and end observations for each id in file2';
run;

* merge pointers to file1.
Since pointer only has 1 obs per id there is no problem merging;

proc sort data=file1;
by id;
run;

data file1;
merge file1 (in=m1)
pointer (in=m2);
by id;
if m1 and m2;
run;

proc print data=file1;
title 'data=file1 with pointers to file2 attached';
run;

* now use the pointers to merge the observation in file2 to file1;
data mer1;
set file1;
do i=start to end;
set file2 point=i;
output;
end;
run;

proc print data=mer1;
title 'Merge of file1 and file2 using pointer method';
run;





Michael

 

[Queryman]

Here is a link to a nice article on how do do this with macros:

http://www2.sas.com/proceedings/sugi27/p089-27.pdf

Michael

 

[6656]

Thanks Michael, I will try it later. It ia a little complicated in the merge statement.
Mike

 

[6656]

It works greatly.