Help using Base SAS procedures

Number of records are different in left join & merge

Reply
Contributor
Posts: 24

Number of records are different in left join & merge

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 ?

Respected Advisor
Posts: 4,173

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

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

Super User
Posts: 7,781

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

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 186 views
  • 0 likes
  • 3 in conversation