Date:         Wed, 5 Nov 2008 12:41:46 -0600
Reply-To:     Warren Schlechte <Warren.Schlechte@TPWD.STATE.TX.US>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Warren Schlechte <Warren.Schlechte@TPWD.STATE.TX.US>
Subject:      Re: SQL Left Join Warning - Join 3 tables
Comments: To: Ed Heaton <EdHeaton@westat.com>
Content-Type: text/plain; charset="us-ascii"

Thanks Ed.  I knew there had to be a way to avoid the WARNING, but the
things I tried wouldn't do it.

Warren Schlechte

-----Original Message-----
From: Ed Heaton [mailto:EdHeaton@westat.com]
Sent: Wednesday, November 05, 2008 12:01 PM
To: Warren Schlechte; SAS-L@LISTSERV.UGA.EDU
Subject: RE: SQL Left Join Warning - Join 3 tables

Try the following:

create table ld50_parms as
select *
from tmp1
natural left join tmp2
natural left join tmp3
;



Ed

Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1650 Research Boulevard, TB-286, Rockville, MD 20850-3195
Voice: (301) 610-4818                 Fax: (301) 294-2085
mailto:EdHeaton@Westat.com          http://www.Westat.com




-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Warren Schlechte
Sent: Wednesday, November 05, 2008 12:37 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL Left Join Warning - Join 3 tables


The variable in all the datasets is the variable I am matching on, so it
has to be in all the datasets.

Given that, what can I do to stop from getting the WARNING note.

Warren Schlechte
-----Original Message-----
From: bigD [mailto:diaphanosoma@GMAIL.COM]
Sent: Wednesday, November 05, 2008 10:39 AM
Subject: Re: SQL Left Join Warning - Join 3 tables

You have the same variable name in two different data sets. Sas will
drop the values from the data set on the "right" in this case, and only
keep the variables on the left. Usually not a problem, unless variables
from the two datasets contain different information.



On Nov 5, 10:10 am, Warren.Schlec...@TPWD.STATE.TX.US (Warren
Schlechte) wrote:
> Here is the log from some sql steps I have coded.
>
> I am selecting specific elements from specific rows from a dataset
named
> ld50_est, and then join the data I want into a new table based on the
> variable n_time.
>
> The code works as I want, but I get a warning.
>
> I have tried several things, but cannot seem to get clean running code
> without the warning.
>
> I would appreciate your help.
>
> 31         proc sql;
> 32         create table tmp1 (drop=_type_)as
> 33         select n_time, intercept, trtmnt, ld50, _type_
> 34         from ld50_est
> 35         where _type_="PARMS";
> NOTE: Table WORK.TMP1 created, with 4 rows and 4 columns.
>
> 36
> 37         create table tmp2 as
> 38         select n_time, var_b0
> 39         from ld50_est
> 40         where var_b0 ^=.;
> NOTE: Table WORK.TMP2 created, with 4 rows and 2 columns.
>
> 41
> 42         create table tmp3 as
> 43         select n_time, var_b1, cov_b0b1
> 44         from ld50_est
> 45         where var_b1 ^=.;
> NOTE: Table WORK.TMP3 created, with 4 rows and 3 columns.
>
> 46
> 47         create table ld50_parms as
> 48         select *
> 49         from (tmp1
> 50         left join tmp2
> 51         on tmp1.n_time=tmp2.n_time)
> 52         left join tmp3
> 53         on tmp2.n_time=tmp3.n_time
> 54         ;
> WARNING: Variable N_Time already exists on file WORK.LD50_PARMS.
> WARNING: Variable N_Time already exists on file WORK.LD50_PARMS.
>
> Warren Schlechte
> HOH Fisheries Science Center
> 5103 Junction Hwy
> Mt. Home, TX 78058
> Phone 830.866.3356 x214
> Fax     830.866.3549