Date:         Tue, 7 Jun 2005 15:05:54 -0400
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: merge more than one table with more than one key
Comments: To: sas-l@uga.edu

gscsrc@hotmail.com wrote:
> I have 3 tables, table one, two, three.
>
> table one key field is A
> table two key fields are A and B
> table three key fields are A, B and C
>
> how do I join these 3 tables together in one sas data set keep their
> relationships, and do not get duplicates or nulls.

To clarify, I used this data.  The join is based on the presumptions that
data has primary keys and foreign key relationships being enforced.

proc sql;
  alter table three drop foreign key _fk0001_;
  alter table two   drop foreign key _fk0001_;

data one;
  do a = 1 to 10;
    one_sat + 1;
    output;
  end;
run;

data two;
  do a = 1 to 10;
  do b = 1 to 10;
    two_sat + 1;
    output;
  end;
  end;
run;

data three;
  do a = 1 to 10;
  do b = 1 to 10;
  do c = 1 to 10;
    three_sat + 1;
    output;
  end;
  end;
  end;
run;

proc sql;
  alter table one   add primary key (a);
  alter table two   add primary key (a,b)
                    add foreign key (a) references one;
  alter table three add primary key (a,b,c)
                    add foreign key (a,b) references two;


  create table _123 as
  select distinct three.*, two.*, one.* from
  three
  left join
  two
  on three.a=two.a and three.b=two.b
  left join
  one
  on
  two.a=one.a
;

--
Richard A. DeVenezia
http://www.devenezia.com/