turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Difference between merging by Proc sql in SAS and ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-11-2016 07:38 PM

Hello everyone,

I tried to merge two datasets:

have1 with two common variables: var11 and var12

have2 with two common variables: var21 and var22

So I used the following code:

proc sql;

create table want

as select L.*, R.*

from have1 as L full outer join have2 as R

on L.var11=R.var21 and L.var12=R.var22;

quit;

Then I tried to do the same thing in Stata. I change the common variables in both dataset to var1 and var2, and ran the following code:

use file1

merge m:m var1 var2 using file2

There is a conflict in the result of these two approaches. The first code gives me a result with 10M observation, but the output of the second code has only 300K observations.

I read the manuals but I couldn't figure out what is the reason that I get two different results by using these methods.

I appreciate if you could help.

Accepted Solutions

Solution

01-12-2016
12:30 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to m1986MM

01-12-2016 06:43 AM

According to the STATA documentation of their merge statement (http://www.stata.com/manuals13/dmerge.pdf), "m:m specifies a many-to-many merge and is a bad idea."

What they describe is exactly what happens in a SAS data step if you merge two datasets by a key with repeats of BY values in both datasets (i.e. one-to-one within the BY group, as pointed out by @Astounding, and repeating the last observation of the shorter BY group, if any).

In contrast, when PROC SQL joins two tables, it always creates a Cartesian product of the two tables (i.e. each row of the first combined with all rows of the second) as the internal "input to the rest of the query" (PROC SQL documentation, section "Joining Tables"). Please see @Astounding's example or the example "Full Outer Join" in the linked documentation.

So, if a BY group in table L has *m* observations and there is a matching BY group in table R with *n* observations, the STATA merge of the two tables would result in max(*m*,*n*) observations for that BY group, whereas the full outer join of PROC SQL would produce *m*·*n* observations. Please note that *m*·*n > *max(* m,n*) if and only if

Here's what the STATA documentation linked above says about such merges:

- "Because m:m merges are such a bad idea, we are not going to show you an example."
- "First, if you think you need to perform an m:m merge, then we suspect you are wrong."

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to m1986MM

01-11-2016 09:14 PM

The second combination using MERGE doesn't look like a real SAS program, but let's assume that you successfully completed a MERGE.

The number of observations would be different if there are multiple observations in both data sets with the same values for VAR1 and VAR2. SQL gives you all combinations, but MERGE matches observation by observation. Let's take a simpler case where you are just joining by 1 variable.

Have1:

VAR1 tracker1

1 A

1 B

1 C

Have2:

VAR1 tracker2

1 X

1 Y

1 Z

If you were to MERGE these two data sets, you would get observations matched 1 by 1:

VAR1 tracker1 tracker2

1 A X

1 B Y

1 C Z

You would also get a note in the log about more than one data set containing repeated values for the BY variable.

If you were to join with SQL, you would get all combinations:

VAR1 tracker1 tracker2

1 A X

1 A Y

1 A Z

1 B X

1 B Y

1 B Z

1 C X

1 C Y

1 C Z

That's just how the tools work.

Good luck.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

01-11-2016 09:53 PM

Thanks for your respond, but as you said it is not a sas code, it's STATA. I want to compare these two commands, the first one in SAS and the second one in STATA.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to m1986MM

01-11-2016 10:09 PM

Oops ... missed the part about Stata.

I'm not sure how Stata would combine the data sets, but SAS would definitely multiply the number of matches by giving you all combinations as illustrated in my example. Depending on the characteristics of Stata, that might still be the difference between the two results.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to m1986MM

01-11-2016 10:36 PM

I don't know the answer, but I have an idea on how I'd find it.

Generate two small sample data sets, that represent your merge type, in this case many to many.

Determine what the expected output is.

Join using both Stata and SAS

See which is correct

With a smaller dataset it's more likely you can determine what type of join Stata is using.

If you were trying to google, I would look for how Stata handles the many to many merge. The SAS SQL Full outer join is pretty explicit and common, but what Stata is doing may differ. My 2 minute search, didn't yield anything obvious, but I'm not a Stata user at all.

Solution

01-12-2016
12:30 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to m1986MM

01-12-2016 06:43 AM

According to the STATA documentation of their merge statement (http://www.stata.com/manuals13/dmerge.pdf), "m:m specifies a many-to-many merge and is a bad idea."

What they describe is exactly what happens in a SAS data step if you merge two datasets by a key with repeats of BY values in both datasets (i.e. one-to-one within the BY group, as pointed out by @Astounding, and repeating the last observation of the shorter BY group, if any).

In contrast, when PROC SQL joins two tables, it always creates a Cartesian product of the two tables (i.e. each row of the first combined with all rows of the second) as the internal "input to the rest of the query" (PROC SQL documentation, section "Joining Tables"). Please see @Astounding's example or the example "Full Outer Join" in the linked documentation.

So, if a BY group in table L has *m* observations and there is a matching BY group in table R with *n* observations, the STATA merge of the two tables would result in max(*m*,*n*) observations for that BY group, whereas the full outer join of PROC SQL would produce *m*·*n* observations. Please note that *m*·*n > *max(* m,n*) if and only if

Here's what the STATA documentation linked above says about such merges:

- "Because m:m merges are such a bad idea, we are not going to show you an example."
- "First, if you think you need to perform an m:m merge, then we suspect you are wrong."

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FreelanceReinhard

01-12-2016 01:02 PM

Thanks,

I also did an example and it clearly proved your explanation. Just to document it here, just in case that someone needs it one day.

If we want to merge these two tables by ID and Weight:

Name ID Weight

Sara 11 110

Rose 11 110

ID Weight Height

11 110 6

11 110 5

The SAS output will be:

Name ID Weight Height

Sara 11 110 6

Sara 11 110 5

Rose 11 110 6

Rose 11 110 5

The STATA output will be:

Name ID Weight Height

Sara 11 110 6

Rose 11 110 5