topic Number of records are different in left join & merge in SAS Procedures

Number of records are different in left join & merge

KP12 — Fri, 13 Mar 2015 05:24:09 GMT

Hi all,

I have a following query, which returns 636302 number of records:

proc sql;

create table application as

select distinct a.*, b.App1, b.App2, c.EMP1

from app1 a left join app2 b on (a.Application_Number=b.AA_No) left join app3 c on (a.Application_Number=c.APP_ID);

quit;

I tried rewriting the above query using merge as follows, but this query is returning 635610 number of records:

data application;

merge app1 (IN=a keep=_all_) app2 (in=b keep= App1 App2 AA_No rename=(AA_No=Application_Number))

app3 (in=c keep=EMP1 APP_ID rename=(APP_ID=Application_Number));

by Application_Number;

if a;

RUN;

Can someone please tell me if I'm missing something above in the merge query because of which a difference of 692 records is coming ?

Re: Number of records are different in left join & merge

Patrick — Fri, 13 Mar 2015 06:22:53 GMT

A SAS merge doesn't work exactly the same like a SQL join and you will get different results for many:many relationships.

Please refer to this post and the links provided there

Re: Number of records are different in left join & merge

Kurt_Bremser — Fri, 13 Mar 2015 06:31:05 GMT

If you have 3 records for a key in dataset1, and 2 records for a key in dataset2, a merge in a data step by that key will give you 3 records, the join in SQL will give you 6 (cartesian product).