Many to many merge

[Paris]

Urgent request.

Can anybody help me with many to many merge?
I have two datasets A and B, each has six variables (5 explanatory variables: agegroups, sex, service type, service group, and year) and one outcome variable (hospital admissions rate). I want to calculate age-sex-service type-service group-year specific rate ratio but don't know how to merge these two files together as there is no unique matching ID and two files have difference number of rows.


Thanks

[sbb]

Provide data sample(s) and the desired result, in your post, for a more effective and timely response to your question. Also, to accomplish a MERGE in SAS, there are some data input requirements, depending on the desired results.

Below, you will find links to some SAS support website http://support.sas.com/ technical paper references for you to consider and review.

Scott Barry
SBBWorks, Inc.


http://www2.sas.com/proceedings/sugi27/p052-27.pdf

http://www2.sas.com/proceedings/sugi25/25/cc/25p109.pdf


Reading, Combining, and Modifying SAS Data Sets - SAS DOC with subsections to review:
http://support.sas.com/documentation/cdl/en/lrcon/59522/HTML/default/a001125856.htm

[Paris]

Many thanks for your prompt response.

For example:

Dataset A
agegrp sex stay_type rate1
1 1 1 10
2 1 1 10
3 1 1 8
4 1 1 9
5 1 1 7
1 2 1 9
2 2 1 10
3 2 1 11
4 2 1 13
5 2 1 7
1 1 2 15
2 1 2 17
3 1 2 9
4 2 2 8

Dataset B
agegrp sex stay_type rate2
1 1 1 11
2 1 1 12
3 1 1 7
4 1 1 9
5 1 1 10
1 2 1 9
2 2 1 10
3 2 1 11
4 2 1 15
5 2 1 7
1 1 2 16
2 1 2 17
3 2 2 9
4 2 2 8


I need to calculate rate ratio (rate2/rate1) for each age-sex-stay type specific group.

Thanks.

[sbb]

With multiple observations for a unique BY variable set, you will need to use some SAS procedure (MEANS, SUMMARY, UNIVARIATE) to generate a reduced observation set before using a DATA step MERGE process.

Suggest you start out with getting your data into SAS members using a SAS DATA step, then explore what "data summarization or analysis" procedure will work for you, and then review the DATA step process for merging using a BY statement, so you can perform your calculation.

I encourage you to use the DOC and technical paper references cited previously.

Scott Barry
SBBWorks, Inc.

[Paris]

Thanks, Scott.

What you have suggested is absolutely right. I have done proc summary to generate maximally one record for each age-sex-service specific group. The problems are that (1) I need to match the two datasets by a set of variables rather than one and (2) there are many unmatched missing groups bwteen dataset1 and dataset2.

[sbb]

Okay. A SAS DATA step MERGE with a BY statement, which supports multiple SAS variables. It's unclear to me what you're expecting from SAS software, frankly? Again, I encourage you to show us *ALL* of your sample data, including the summarized level, and also the outcome of some MERGE process, for additional feedback.

Scott Barry
SBBWorks, Inc.

[Patrick]

Hi Paris
Looking at the example data you give us the combination of the variables agegrp,sex,stay_type (composite key) is unique. The relation between the two tables is 1:1.
Have a look at the example below.
I used Proc SQL - you could do the same by first sorting the tables A and B by agegrp sex stay and then merge the sorted tables (merge A B; by agegrp sex stay;).
HTH
Patrick

Data A;
input agegrp sex stay_type rate1;
datalines;
1 1 1 10
2 1 1 10
3 1 1 8
4 1 1 9
5 1 1 7
1 2 1 9
2 2 1 10
3 2 1 11
4 2 1 13
5 2 1 7
1 1 2 15
2 1 2 17
3 1 2 9
4 2 2 8
;
Data B;
input agegrp sex stay_type rate2;
datalines;
1 1 1 11
2 1 1 12
3 1 1 7
4 1 1 9
5 1 1 10
1 2 1 9
2 2 1 10
3 2 1 11
4 2 1 15
5 2 1 7
1 1 2 16
2 1 2 17
3 2 2 9
4 2 2 8
;

proc sql;
/* create table CombinedAB as*/
select coalesce(l.agegrp,r.agegrp) as agegrp
,coalesce(l.sex,r.sex) as sex
,coalesce(l.stay_type,r.stay_type) as stay_type
,l.rate1
,r.rate2
from work.a l full join work.b r
on l.agegrp=r.agegrp and l.sex=r.sex and l.stay_type=r.stay_type
order by agegrp,sex,stay_type;
quit;