BookmarkSubscribeRSS Feed
Bennettr99
Fluorite | Level 6

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

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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

LinusH
Tourmaline | Level 20
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.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 986 views
  • 1 like
  • 3 in conversation