topic Re: Many to many merge in SAS Procedures

Many to many merge

Paris — Thu, 18 Sep 2008 13:14:38 GMT

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

Re: Many to many merge

sbb — Thu, 18 Sep 2008 13:29:10 GMT

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

Re: Many to many merge

Paris — Thu, 18 Sep 2008 13:38:52 GMT

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.

Re: Many to many merge

sbb — Thu, 18 Sep 2008 13:47:25 GMT

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.

Re: Many to many merge

Paris — Thu, 18 Sep 2008 14:05:41 GMT

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.

Re: Many to many merge

sbb — Thu, 18 Sep 2008 14:13:19 GMT

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.

Re: Many to many merge

Patrick — Sat, 20 Sep 2008 01:48:19 GMT

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;