CODEWITHSUNDEEP
sasproc-sql
ved_null_
ved_null_

Reputation: 71

is there any alternates to merge two datasets in SAS apart from Proc Sql and Merge statement?

i want to merge two datasets, but without using merge statement and Proc sql,can i do this? is there any way to do the same

Upvotes: 0

Views: 224

Answers (3)

SASGeek
SASGeek

Reputation: 1

There are total 5 ways to Merge two datasets:

  1. Proc SQL
  2. Data Merge
  3. Proc Format
  4. Arrays
  5. Hash Objects

Selection of the process depends on the size of the datasets and the primary key used.

Proc Format and Hash Object has been proved as the best method to merge large datasets with lesser Run Time.

Upvotes: 0

Zfunk
Zfunk

Reputation: 1193

Although not a merge in the truest sense, if you have one large dataset and one small dataset, you can read the smaller DS in as a format, and then use proc format to put the values onto the larger one.

Upvotes: 2

mvherweg
mvherweg

Reputation: 1283

Yes there is: a join using hash tables.
See this document for an example: http://www.nesug.org/proceedings/nesug06/dm/da07.pdf

Advantages:

  • Faster in some cases

Disadvantages:

  • One of the tables needs to fit in memory
  • Syntax is very un-SAS-like (being closer to languages such as java)
  • Not everybody is familiar with the concept, certainly novice SAS users (can be problem for maintenance)


In my opinion, hash join is only useful in a very limited set of use cases. One example is where following 2 conditions are met:

  1. You need to join information from a huge table with a small (easily fits in memory) table.
  2. The large table is not sorted on the join variables and there is no added value in having it sorted.

When the small table gets extremely small (e.g. only 10 key values), i would maybe consider some approach using 2 macro variables and 2 arrays. This because the code would be as performant and easier to recognize as SAS by other people who might come after me and need to maintain it.

Conclusion: judging from the way the question is framed, you should go for SAS data step merge or proc sql join.

Upvotes: 3

Related Questions