Date:         Tue, 4 Mar 2003 10:15:36 -0500
Reply-To:     "Delaney, Kevin P." <khd8@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Delaney, Kevin P." <khd8@CDC.GOV>
Subject:      Re: Natural Join
Comments: To: Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Content-Type: text/plain

Documented in v9 though:

http://v9doc.sas.com/cgi-bin/sasdoc/cgigdoc?file=../proc.hlp/tw5520sqljoined
.htm#a002108915

Kevin, I assume it's safe to say that Statecode is the same type and length
in both the State and County tables??

Seems like it is not working correctly in 8.2, when I submit the code below
in 8.2 I get an extra statecode variable, but in v9 I only have three
variables statecode  regdistrict and county, in the View County


data test1;
input statecode;
datalines;
1
2
3
4
;
run;

data test2;
input statecode regdistrict;
datalines;
1 1
1 2
1 3
2 2
2 1
2 3
4 1
;
run;

data test3;
input statecode regdistrict county;
datalines;
1 1 4
1 1 3
1 1 2
1 1 1
1 2 1
1 2 2
1 3 1
2 1 1
2 1 2
;
run;

proc sql;
create view regview as
select * from test2 natural join test1;

create view county as
select * from regview natural join test3;
quit;


-----Original Message-----
From: Kevin Myers [mailto:KevinMyers@AUSTIN.RR.COM]
Sent: Tuesday, March 04, 2003 9:34 AM
Subject: Re: Natural Join


Perhaps natural join may be officially new for V9, but it also exists in
8.2.

----- Original Message -----
From: "WIELKI Andre" <wielki@ined.fr>
To: "Kevin Myers" <KevinMyers@AUSTIN.RR.COM>
Sent: Tuesday, March 04, 2003 4:22 AM
Subject: Re: Natural Join


> Kevin
> Is this something new from v9?
> Natural join
>
> Andre
>
>
> At 23:34 03/03/2003 -0600, you wrote:
> >Hi folks,
> >
> >There's something about natural join behavior in SAS that I don't
> >understand.  I have the following three tables (some columns omitted
> >for
> >simplicity):
> >
> >Table: state
> >Columns: stateCode
> >
> >Table: regDistrict
> >Columns: stateCode, regDistCode
> >
> >Table: county
> >Columns: stateCode, countyCode, countyName, regDistCode
> >
> >
> >Now I create two views:
> >
> >create view regDistrictView as
> >    select * from regDistrict natural join state;
> >
> >create view countyView as
> >    select * from county natural join regDistrictView;
> >
> >
> >The resulting views contain columns as follows:
> >
> >View: regDistrictView
> >Columns: stateCode, regDistCode
> >
> >View: countyView
> >Columns: stateCode, regDistCode, stateCode, countyCode, countyName
> >
> >
> >Now my question is, why is the stateCode column duplicated in
> >countyView, when it is NOT duplicated in regDistrictView???  Thanks
> >in advance for
any
> >tips.
> >
> >s/KAM
>
>
> ___________
> WIELKI  Andre
> INED - Service Informatique
> 133 Bd Davout,
> 75 980 Paris  Cedex 20
> FRANCE
> Tel: 01 56 06 21 54
>