topic Re: MERGE in SAS Programming

MERGE

deleted_user — Fri, 15 Jan 2010 07:43:43 GMT

How would code a merge that will keep only observations that have match both the set.

Re: MERGE

deleted_user — Fri, 15 Jan 2010 07:50:51 GMT

Try this
first sort the two data sets basis the matching variable then apply the code below;

data target_dataset;
merge src_dataset1 (in = a) src_dataset2 (in = b);
by ;
if a & b then output;
run;

Re: MERGE

Peter_C — Sat, 16 Jan 2010 09:30:29 GMT

Leena,
have you any rules for handling data which could match more than one row on either side?

PeterC

Re: MERGE

DanielSantos — Mon, 18 Jan 2010 08:52:27 GMT

For a 1-to-N match stick with the data/merge.

For a N-to-N match, the easiest way would be coding it through SQL.

To my knowledge, data/merge N-way match is actually possible, but it involves using an index and a requires one of the tables to be quite small, on top of that the implementation is quite obscure.

If you are running 9.2 you could try the new features of the hash object, being one, the possibility to load into the hash object multiple obs with the same key.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: MERGE

Peter_C — Mon, 18 Jan 2010 09:22:22 GMT

Daniel
since the data-step merge is easier than SQL unless n-n join/merge is required, I had to ask my question
Regards
PeterC

Re: MERGE

DanielSantos — Mon, 18 Jan 2010 09:55:47 GMT

Yes, Peter.

I was just discussing my point of view of the 1-N/N-N scenarios.

Your question makes perfect sense to me, I would go for the SQL only for a N-to-N match. I also found Merge easier than SQL, and a lot more "controlable".

Cheers from Portugal.

Daniel Santos @ www.cgd.pt