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;