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
- /
- SAS Procedures
- /
- Merging Many-to-Many Datasets

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

06-06-2008 12:29 AM

Hi,

I have done one-to-one and one-to-many match merge using SAS. However, I'm having trouble doing a many-to-many match merge. I hope someone can help me. For simplicity, assume that I have the following datasets:

First datasets:

VarA VarB

a1 a

a1 b

a1 c

a2 a

a2 d

a3 b

a3 f

Second datasets:

VarC VarA

c1 a1

c1 a2

c2 a1

c2 a3

c3 a1

Is there any way for me to merge the two datasets so that I will have the following:

VarC VarA VarB

c1 a1 a

c1 a1 b

c1 a1 c

c1 a2 a

c1 a2 d

c2 a1 a

c2 a1 b

c2 a1 c

c2 a3 b

c2 a3 f

c3 a1 a

c3 a1 b

c3 a1 c

I sorted both datasets by VarA and then merged the datasets by VarA but the results are not what I wanted. I think the above can be done as there is a logical sequence to the matching but I can't seem to do it. Can someone please help? Thanks!

I have done one-to-one and one-to-many match merge using SAS. However, I'm having trouble doing a many-to-many match merge. I hope someone can help me. For simplicity, assume that I have the following datasets:

First datasets:

VarA VarB

a1 a

a1 b

a1 c

a2 a

a2 d

a3 b

a3 f

Second datasets:

VarC VarA

c1 a1

c1 a2

c2 a1

c2 a3

c3 a1

Is there any way for me to merge the two datasets so that I will have the following:

VarC VarA VarB

c1 a1 a

c1 a1 b

c1 a1 c

c1 a2 a

c1 a2 d

c2 a1 a

c2 a1 b

c2 a1 c

c2 a3 b

c2 a3 f

c3 a1 a

c3 a1 b

c3 a1 c

I sorted both datasets by VarA and then merged the datasets by VarA but the results are not what I wanted. I think the above can be done as there is a logical sequence to the matching but I can't seem to do it. Can someone please help? Thanks!

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

06-06-2008 02:05 AM

proc sql;

create table manytomany as

select b.*, a.VarB

from secondset b, firstset a

where b.VarA=a.VarA;

create table manytomany as

select b.*, a.VarB

from secondset b, firstset a

where b.VarA=a.VarA;

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

06-10-2008 03:06 AM

Hi Vasile,

Thank you so much! I used your proc sql statement and got the dataset that I required. Cheers!

Thank you so much! I used your proc sql statement and got the dataset that I required. Cheers!