Date: Wed, 5 Nov 2008 21:30:05 -0800
Reply-To: Kevin Y <kevin77711@YAHOO.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Kevin Y <kevin77711@YAHOO.COM>
Subject: Re: SQL Left Join Warning - Join 3 tables
Content-Type: text/plain; charset=iso-8859-1
You have to use coalesce to remove the warnings:
Proc sql;
create table ld50_parms as
select
coalesce(tmp1.n_time, tmp2.n_time,tmp3.n_time) as n_time,
tmp1.intercept,
tmp1.trtmnt,
tmp1.ld50,
tmp1._type_,
tmp2.ld50_es,
tmp3.var_b1,
tmp3.cov_b0b1
tmp3.ld50_est
from (tmp1
left join tmp2
on tmp1.n_time=tmp2.n_time)
left join tmp3
on tmp2.n_time=tmp3.n_time
;
quit;
________________________________
From: Warren Schlechte <Warren.Schlechte@TPWD.STATE.TX.US>
To: SAS-L@LISTSERV.UGA.EDU
Sent: Wednesday, November 5, 2008 11:10:00 PM
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
| 
