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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.