Desktop productivity for business analysts and programmers

Merge and SQL

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Merge and SQL

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


Accepted Solutions
Solution
‎09-28-2016 08:57 AM
Super User
Posts: 7,429

Re: Merge and SQL

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎09-28-2016 08:57 AM
Super User
Posts: 7,429

Re: Merge and SQL

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: Merge and SQL

Hi KurtBremser

 

 

 

Super User
Posts: 19,105

Re: Merge and SQL

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 323 views
  • 0 likes
  • 3 in conversation