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 ?
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).
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.