BookmarkSubscribeRSS Feed
Paris
Calcite | Level 5
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
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Opal | Level 21
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 965 views
  • 0 likes
  • 3 in conversation