Hello Team,
I am working on one of migration and has below statement
data strat.rep3;
merge strat.rep1 (in=uio) strat.rep2 (in=bnm);
by wclientcode wGroup mth ptpgroup;
if uio or bnm;
run;
Can someone please guide me what can be possible SQL statement for same ?
SELECT #rep1.wClientCode, #rep1.wGroup, #rep1.mth, #rep1.ptpGroup, #rep1.no_docapt, #rep1.ptpamtcapt,
#rep2.ptpamtdue, #rep2.no_kept, #rep2.ptpkeptamt, #rep2.no_dodue
FROM #rep1 INNER JOIN #rep2
ON #rep1.wClientCode = #rep2.wClientCode
AND #rep1.wGroup = #rep2.wGroup
AND #rep1.mth = #rep2.mth
AND #rep1.ptpGroup = #rep2.ptpGroup
But this statement not matching records of SAS output and SQL output. Can someone please guide me what is wrong here ?
Please let me know
Thanks
Your SQL code won't execute, as # is not allowed in SAS names.
There is a basic difference between SQL and the datastep merge, when there is a m:n relationship between the datasets. SQL will yield m*n records, while the merge will yield max(m,n) records. So you need to inspect both datasets for multiple occurences of a key variable combination.
The condition
if uio or bnm;
suggests you should be trying an outer join.
Your SQL code won't execute, as # is not allowed in SAS names.
There is a basic difference between SQL and the datastep merge, when there is a m:n relationship between the datasets. SQL will yield m*n records, while the merge will yield max(m,n) records. So you need to inspect both datasets for multiple occurences of a key variable combination.
The condition
if uio or bnm;
suggests you should be trying an outer join.
Hi KurtBremser
Yes, its working with full outer join.
So
if uio or bnm - Full Outer Join
if uio and bnm - Inner Join
if uio - Right Join
If bnm - Left Join
Thank you
Make sure to check results in the case of a Manu to many merge. If this isn't an issue with your data feel free to disregard this.
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.