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
- /
- Merge 2 datasets, many to many Match Merge

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
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-27-2011 12:19 AM

Hello,

I know I can do a one-to-many match merge, but can I do a many to many match merge?

Essentially, i have 3 variables to match by, but here's where things get tricky: some of the observations from one set will not have corresponding matches in the second, while other observations will have multiple matches to the second set. The following three sets represent what i'm referring to, with the first two the sets to merge and the last what i want the final set to look like. I think i can do this with proc sql, but am not sure how.

With this first set, the first 2 variables don't appear in the second set. Then come the 3 variables to match by.

The next set starts with the 3 variables to match by, then the variables to add to the final set.

The final set shows how the observations in the first set, where PRP='Out' have no values from fbp and Percent from the second. The other observations, where PRP='Hit' then duplicate themselves to account for the 3 situations which occur when PRP is a 'Hit'.

I know this is a touch confusing, so please feel free to ask questions.

[pre]

data set1;

input Hit Z zn BSi$ PRP$;

datalines;

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

14 87.5 23 R Out

14 87.5 23 R Hit

14 87.5 23 R Hit

14 87.5 23 R Out

run;

data set2;

input zn BPT BBV DP$ BSi$ PRP$ fbp Percent;

datalines;

23 L Hit 3 .8

23 L Hit 4 .1

23 L Hit 9 .1

23 R Hit 3 .6

23 R Hit 4 .3

23 R Hit 9 .1

run;

data set3;

5 100 23 L Out . .

5 100 23 L Hit 3 .8

5 100 23 L Hit 4 .1

5 100 23 L Hit 9 .1

5 100 23 L Out . .

5 100 23 L Hit 3 .8

5 100 23 L Hit 4 .1

5 100 23 L Hit 9 .1

5 100 23 L Out . .

14 87.5 23 R Out . .

14 87.5 23 R Hit 3 .6

14 87.5 23 R Hit 4 .3

14 87.5 23 R Hit 9 .1

14 87.5 23 R Hit 3 .6

14 87.5 23 R Hit 4 .3

14 87.5 23 R Hit 9 .1

14 87.5 23 R Out . .

run;

[/pre]

Thanks,

C

Message was edited by: CharlesR Message was edited by: CharlesR

I know I can do a one-to-many match merge, but can I do a many to many match merge?

Essentially, i have 3 variables to match by, but here's where things get tricky: some of the observations from one set will not have corresponding matches in the second, while other observations will have multiple matches to the second set. The following three sets represent what i'm referring to, with the first two the sets to merge and the last what i want the final set to look like. I think i can do this with proc sql, but am not sure how.

With this first set, the first 2 variables don't appear in the second set. Then come the 3 variables to match by.

The next set starts with the 3 variables to match by, then the variables to add to the final set.

The final set shows how the observations in the first set, where PRP='Out' have no values from fbp and Percent from the second. The other observations, where PRP='Hit' then duplicate themselves to account for the 3 situations which occur when PRP is a 'Hit'.

I know this is a touch confusing, so please feel free to ask questions.

[pre]

data set1;

input Hit Z zn BSi$ PRP$;

datalines;

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

14 87.5 23 R Out

14 87.5 23 R Hit

14 87.5 23 R Hit

14 87.5 23 R Out

run;

data set2;

input zn BPT BBV DP$ BSi$ PRP$ fbp Percent;

datalines;

23 L Hit 3 .8

23 L Hit 4 .1

23 L Hit 9 .1

23 R Hit 3 .6

23 R Hit 4 .3

23 R Hit 9 .1

run;

data set3;

5 100 23 L Out . .

5 100 23 L Hit 3 .8

5 100 23 L Hit 4 .1

5 100 23 L Hit 9 .1

5 100 23 L Out . .

5 100 23 L Hit 3 .8

5 100 23 L Hit 4 .1

5 100 23 L Hit 9 .1

5 100 23 L Out . .

14 87.5 23 R Out . .

14 87.5 23 R Hit 3 .6

14 87.5 23 R Hit 4 .3

14 87.5 23 R Hit 9 .1

14 87.5 23 R Hit 3 .6

14 87.5 23 R Hit 4 .3

14 87.5 23 R Hit 9 .1

14 87.5 23 R Out . .

run;

[/pre]

Thanks,

C

Message was edited by: CharlesR Message was edited by: CharlesR

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

Posted in reply to CharlesR

04-27-2011 03:31 AM

Hi

Looks to me as if the data for set2 don't match the mapping in the input statement.

Anyway: Below example should point you into the right direction.

data set1;

input Hit Z zn BSi $ PRP $ ;

datalines;

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

14 87.5 23 R Out

14 87.5 23 R Hit

14 87.5 23 R Hit

14 87.5 23 R Out

;

run;

data set2;

input zn BSi $ PRP $ var1 var2 8.;

datalines;

23 L Hit 3 .8

23 L Hit 4 .1

23 L Hit 9 .1

23 R Hit 3 .6

23 R Hit 4 .3

23 R Hit 9 .1

;

run;

proc sql;

create table set3 as

select L.*, R.var1, R.var2

from set1 L left join set2 r

on L.zn=R.zn and L.BSi=R.BSi and L.PRP=R.PRP

;

quit;

HTH

Patrick

Looks to me as if the data for set2 don't match the mapping in the input statement.

Anyway: Below example should point you into the right direction.

data set1;

input Hit Z zn BSi $ PRP $ ;

datalines;

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

5 100 23 L Hit

5 100 23 L Out

14 87.5 23 R Out

14 87.5 23 R Hit

14 87.5 23 R Hit

14 87.5 23 R Out

;

run;

data set2;

input zn BSi $ PRP $ var1 var2 8.;

datalines;

23 L Hit 3 .8

23 L Hit 4 .1

23 L Hit 9 .1

23 R Hit 3 .6

23 R Hit 4 .3

23 R Hit 9 .1

;

run;

proc sql;

create table set3 as

select L.*, R.var1, R.var2

from set1 L left join set2 r

on L.zn=R.zn and L.BSi=R.BSi and L.PRP=R.PRP

;

quit;

HTH

Patrick