DATA Step, Macro, Functions and more

difference between merg and proc sql join.

Reply
Frequent Contributor
Posts: 76

difference between merg and proc sql join.

Please expaine how the merg statement  is differe from proc sql join.

Regards

Ashwini

PROC Star
Posts: 7,467

difference between merg and proc sql join.

Much has already been written on the topic.  A nice intro can be found at: http://www2.sas.com/proceedings/sugi30/249-30.pdf

However, a web search for sas sql merge will bring up quite a few more papers that explain even more of the differences and similarities

Regular Contributor
Posts: 233

difference between merg and proc sql join.

Difference 1:

  • Merge takes one record from the first file matches with one record on the second file if they have same column in common.
  • Proc SQL takes one record from the first file matches with all records on the second file if they have same column in common.

Difference 2:

  • Merge - Data sets must be sorted by or indexed on the BY variable prior to merging.
  • Proc SQL - Data sets doesnot need to be sorted or indexed.

Difference 3:

  • Proc SQL - Multiple data sets can be joined in one step with out having common variables in all data sets.

Difference 4:

  • Proc SQL - The maximum number of tables that can be joined at a time is 32.
PROC Star
Posts: 7,467

difference between merg and proc sql join.

Not quite!

Difference 1:

Try the following example.  You will discover that the datastep merge will have the same result as a sql join:

 

data first;

  input sex $ amount;

  cards;

F 18

M 14

;

data second;

  set sashelp.class;

run;

proc sort data=second;

  by sex;

run;

data want;

  merge first second;

  by sex;

run;

However, a many-to-many merge is more difficult to achieve via a datastep merge.

Difference 3:

The same thing is true for a datastep merge.

Regular Contributor
Posts: 233

difference between merg and proc sql join.

For difference 1 I am not saying that the results defer but I am talking about the process behind the scenes.

Super User
Posts: 5,424

difference between merg and proc sql join.

#3: what I think Hima meant was that the join variables can be different of you are joining more than two tables. This cannot not be done in one MERGE step.

/Linus

Data never sleeps
PROC Star
Posts: 7,467

difference between merg and proc sql join.

Maybe I still misunderstand the point.  Doesn't including a rename option take care of that in a merge?

Super User
Posts: 5,424

difference between merg and proc sql join.

Rename most certainly handles a scenario when your matching columns have different names (but have the same content).

But SQL lets you join multiple tables with differern join criteria in the different "join pairs".

I.e. you can join table A and B on ssn, and table B and C on Zip-code.I think this is not possible in a MERGE step.

Data never sleeps
Occasional Contributor
Posts: 6

Re: difference between merg and proc sql join.

Hi

Can you tell me how to do mutiple joins using PROC SQL. Is it by inner query or there is some other process for it?

Frequent Contributor
Posts: 117

difference between merg and proc sql join.

I feel that the main difference is in Merge, it includes numerous steps to merge the data like Sorting the data firstt by using a BY variable and then merge the data sets horizontallay using the same BY variable. But this is good for small amount of data.

If you have millions of records and u do want the same process then Proc Sql will be the option which saves your time and effort by creating simple code in a single step.

Correct me if i am wrong.

Regards,

Vishnu

PROC Star
Posts: 7,467

difference between merg and proc sql join.

Vish33: I don't disagree with you, in principle, but that isn't always the case.  E.g., take a look at: http://communities.sas.com/thread/10055

Valued Guide
Posts: 634

difference between merg and proc sql join.

SQL processes the entire table in memory. As the size of your tables increase you may experience performance degradation.  MERGE processes a row at a time so rarely has memory limitations.

Ask a Question
Discussion stats
  • 11 replies
  • 18356 views
  • 0 likes
  • 7 in conversation