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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.