Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4)

Bennettr99 — Sat, 27 Jan 2018 07:45:00 GMT

I am currently trying to merge 2 large data sets using SQL. I haven't had to merge data of this size before, and I need to find an efficient way to merge the data.

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.

Here are some example observations and variables to show that the data sets have some, but not all variables in common.

Sample Data

home._2004

Year State MSA

2004 NE 00561

2004 AL 15646

2004 AL 45646

home.c2final

Year State Loan_Amount

1999 NE 55000

2004 KS 165000

2005 AK 200000

I have been using:

DATA home._2004;
SET home._2004data (obs=10);
RUN;

DATA home.c2final;
SET home.c2 (obs=50);
RUN;

PROC SQL;
      CREATE TABLE home.merge as
      SELECT *
           FROM home._2004
       OUTER UNION ALL
       SELECT *
           FROM home.c2final;
QUIT;

This works when I am only using a few observations from each, but they are too large to do all together. When I used 10 observations from home._2004 and all observations from home.c2final it takes 10 minutes. I am trying to find a way to make this more efficient. My thought was to find a way to merge a number of observations from home._2004 with all of home.c2final, and have the program do this with the next group of observations until all of the data is merged.

For example if I used 50 observations from home._2004:

Observations 1-50 merge with c2final

Followed by

Observations 51-100 merge with c2final

etc.

I am new to SAS, and I am not sure if this is even a possibility.

Re: Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4)

Kurt_Bremser — Sat, 27 Jan 2018 08:08:29 GMT

Step 1: know your data (also Maxim 3).

Determine their relationship with regard to the join keys (one-to-one, one-to-many, many-to-many).

See if other variables appear in both datasets, and determine what to do with them.

From that information, we can help in finding the best approach.

Re: Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4)

PGStats — Sun, 28 Jan 2018 03:49:05 GMT

OUTER UNION is for concatenating datasets, not merging And ALL is not allowed with OUTER UNION in SAS/SQL. So I suspect that it is not the operation you really want.

topic Re: Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4) in SAS Programming

Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4)

Re: Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4)

Re: Outer Union Merge of 2 Large Data Sets Using SQL (SAS 9.4)