topic Efficiently Merging Two Large Data Sets With Some Common Variables (SAS 9.4) in SAS Programming

Efficiently Merging Two Large Data Sets With Some Common Variables (SAS 9.4)

Bennettr99 — Fri, 26 Jan 2018 22:42:40 GMT

I am trying to find an efficient way to merge two very large data sets. I am new to SAS and I have only ever merged small data sets, so I am not sure where to start.

The first data set, home._2004, contains 45 variables and 67 million observations.

The second data set, home.c2final, contains 73 variables and 24 million observations.

The common variables are Year, MSA, CountyCode, state, and Loan_Amount.

This is the code I would have used if the data wasn't so large:

DATA home.testmerge;
MERGE home._2004 home.c2final;
BY Year MSA CountyCode state Loan_Amount;
RUN;

As you can see, I have very little experience with SAS. From the research I done, there are more efficient ways to merge data of this size, but I do not know where to start with those methods. I was trying to find some way to merge the data using groups of observations from the home._2004 data, but I don't know how to set it up so that after it's done with one group of observations it movies to the next.

For further clarification here are some sample observations from each set. Like I mentioned, I am very new to SAS, so I did not know if there was an easier way to display the data.

Home._2004 Sample:

file:///D:/Research/2004sample.html

Home.c2final Sample:

file:///D:/Research/c2finalsample.html

Re: Efficiently Merging Two Large Data Sets With Some Common Variables (SAS 9.4)

novinosrin — Fri, 26 Jan 2018 22:09:52 GMT

You wrote= " The common variables are Year, MSA, CountyCode, state, and Loan_Amount. "

Why you are merging only by year? and not by Year, MSA, CountyCode, state, Loan_Amount.(is it the right order?)

Can you post a small sample of your req like 5-6 records? Do you have any duplicate entries in any of the tables? If you can let us know more, I am sure you will get very good responses

Re: Efficiently Merging Two Large Data Sets With Some Common Variables (SAS 9.4)

LinusH — Sat, 27 Jan 2018 08:58:32 GMT

If both your data sets are sorted by your BY variables, MERGE is the most efficient way.
What is your expected hit rate on the merge, and so you wish to keep alla observations from both data set in the result?
Also I wonder about your data, MERGE on I individual loan amounts sounds a bit....odd. That sounds like measure, not a key variable.