topic Re: Merge and SQL in SAS Enterprise Guide

Merge and SQL

yudhishtirb — Wed, 28 Sep 2016 12:25:52 GMT

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

Re: Merge and SQL

Kurt_Bremser — Wed, 28 Sep 2016 12:33:26 GMT

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.

Re: Merge and SQL

yudhishtirb — Wed, 28 Sep 2016 12:57:46 GMT

Hi KurtBremser

Yes, its working with full outer join.

if uio or bnm - Full Outer Join

if uio and bnm - Inner Join

if uio - Right Join

If bnm - Left Join

Thank you

Re: Merge and SQL

Reeza — Wed, 28 Sep 2016 13:09:03 GMT

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.