SQL Joins and DATA Step Merges Comparison

[tpchaudhary]

Hi All,

As it is explained in SAS certified professional prep gide for advance programming using SAS 9.4 in chapt.3 on page 84 under section "Comparing SQL Joins and DATA Step Match-Merges", my results in below example isn't giving same result. Can someone explain me about why not  both merge and sql join gives same output as shown in below example?

Thanks a lot in advance!

/******************************************************/

data table1; input X A$ @@; cards; 

2 a1 2 a2

; run;

data table2; input X B$ @@; cards; 

2 b1 2 b2

; run;

proc sort data=table1;by X;run;

proc sort data=table2;by X;run;

data merged;

merge table1 table2;

by X;

run;

proc print data=table1 noobs; title 'Table1'; run;

proc print data=table2 noobs; title 'Table2'; run;

proc print data=merged noobs; title 'Merge Data'; run;

proc sql; title 'SQL Join Data';

select coalesce(a.X, b.X) label='G3', A, B

from table1 as a full join table2 as b

on a.X = b.X ;

quit;

/******************************************************/

[mkeintz]

---

@tpchaudhary wrote:

Hi All,

As it is explained in SAS certified professional prep gide for advance programming using SAS 9.4 in chapt.3 on page 84 under section "Comparing SQL Joins and DATA Step Match-Merges", my results in below example isn't giving same result. Can someone explain me about why not  both merge and sql join gives same output as shown in below example?

Thanks a lot in advance!

Are you saying that the guide is claiming that the two results in your program should be identical?  That I can't believe. 

If these really are the exact programs in the guide, then let us know what the page 84 says about the results.  But if you are not using program examples in the guide, then I suspect there is a meaningful difference between the code in the guide and the code you have created to replicate it.

[tpchaudhary]

Hi There,  Under same topic (P. 84) in book  page 87 it says, "When you add the COALESCE function to the SELECT clause of the PROC SQL outer join, the PROC SQL outer join can produce the same result as a DATA step match-merge." Also, pretty much similar example is on page 88 in the book, which output also has same issue, but book claiming that it is combining rows in the same way. 

I just want to understand that how it is combining same way because for the duplicate value for joining column (X) has only two row in merge output and four rows in sql join output.

Also, can someone suggest me how we can get same result for this particular example in merge and sql both?

Exact book example from page 87 & 88 shown below:

/****************************************************************/

/*DATA Step Match-Merge */

/*Below I created the table 3 and 4 unless someone don't have table access*/

data Three; input X A$ @@; cards;

1 a1 1 a2 2 b1 2 b2 4 d

;run;

data Four; input X B$ @@; cards;

2 x1 2 x2 3 y 5 v

;run;

/***************************************************************************/

[Kurt_Bremser · Accepted solution]

the PROC SQL outer join can produce the same result as a DATA step match-merge.

(emphasis by me)

A data step match merge will only create the same result as the SQL join when you have a one-to-one or one-to-many relationship; with many-to-many, the results will always differ.

What the book wants to say is that, in a certain match, the COALESCE function will create the same value overwrite sequence that the data step does. In SQL, the COALESCE function does it, in the DATA step, the sequence in which observations are read into the PDV.

[tpchaudhary]

Thanks Kurt for clarifying that, "A data step match merge will only create the same result as the SQL join when you have a one-to-one or one-to-many relationship; with many-to-many, the results will always differ."

In this situation example is right, and because of many to many relationship both merge and sql join can't give same results.
Thanks a lot!