Date:         Wed, 5 Nov 2008 12:46:05 -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: "Catley, Dennis" <dcatley@exchange.vt.edu>
Content-Type: text/plain; charset="us-ascii"

While it makes sense that this would work, when I tried it I got an
error stating that variable name tmp2.n_time and tmp3.n_time were not
valid variable names.

So, I just went with using the explicit naming instead of the star.

Thanks.
Warren Schlechte

From: Catley, Dennis [mailto:dcatley@exchange.vt.edu]
Sent: Wednesday, November 05, 2008 12:29 PM
To: Warren Schlechte
Subject: RE: SQL Left Join Warning - Join 3 tables

That warning results from the use of the *, not a big deal. SAS is just
letting you know that you're building redundancy into your created
table, by constructing it with 3 columns of the same name.

But you can code a drop after your table name such as

create table ld50_parms (drop = tmp2.n_time tmp3.n_time) as

The alternative is to be explicit Re which columns you want/need from
the 'right side tables'.

The two drops columns are still able to be used in the syntax as you now
have it, they're just not kept in the result set.

--- Dennis

Dennis Catley
Associate Director - Institutional Research
Virginia Tech
128 Smyth Hall - Mailcode 0433
Blacksburg, VA.  24061
dcatley@vt.edu
(540) 231-2623



-----Original Message-----
From: Warren Schlechte [mailto:Warren.Schlechte@TPWD.STATE.TX.US]
Sent: Wednesday, November 05, 2008 10:10 AM
Subject: SQL Left Join Warning - Join 3 tables

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