topic Re: Problem with Match Merge in SAS Procedures

Problem with Match Merge

dan1775 — Mon, 17 Feb 2014 22:26:04 GMT

I've been using match merge for some time now without (known) issues. However, I have a problem with a set I'm running today.

My data sets and code:

DATA M;

INPUT VAR1 VAR2 ;

DATALINES;

x a

x b

x c;

DATA N;

INPUT VAR1 VAR3 ;

DATALINES;

x s

x t ;

DATA P;

MERGE M N;

BY VAR1

The desired result is as follows, and what I'm used to seeing for match merges:

VAR1 VAR2 VAR3

x a s

x a t

x b s

x b t

x c s

x c t

Instead I'm getting something I've never seen before. SAS appears to be merging the two sets by matching the first record of M with the first record of N, second to second. etc. But apprently once all the values of VAR3 have been used it simply repeats the last. As follows:

VAR1 VAR2 VAR3

x a s

x b t

x c t

I've never seen (or noted) this behavior on a match merge before. Perhaps I'm missing something - I'm self-taught and don't know where the holes are in my knowledge.

Thanks!

Re: Problem with Match Merge

PGStats — Mon, 17 Feb 2014 22:42:49 GMT

At each iteration of a datastep merge, a new observation is read from both datasets, if available within the BY group. The result you got is just what is expected. The easiest way to get the product of the two datasets within BY groups is with proc SQL :

proc sql;

create table p as

select m.*, n.var3

from m inner join n

on m.var1=n.var1;

quit;

Re: Problem with Match Merge

dan1775 — Mon, 17 Feb 2014 22:53:37 GMT

Thanks. I'd have to look back at my earlier work; I muyst have been doing one-to-many matches instead of a many-to-many like this one.