Peter,

The compiler seems to agree with you, but I was always under the impression that the variables in a SELECT clause always had to come from the FROM datasets in any query, subquery or otherwise.

However, this log convinces me that you are right.

969 data w ; 970 do x = 1 to 5 ; 971 id = 8 - x ; 972 output ; 973 end ; 974 run ;

NOTE: The data set WORK.W has 5 observations and 2 variables. NOTE: DATA statement used: real time 0.04 seconds

975 976 data q ; 977 y = 99 ; output ; 978 y = 98 ; output ; 979 run ;

NOTE: The data set WORK.Q has 2 observations and 1 variables. NOTE: DATA statement used: real time 0.05 seconds

980 981 proc sql ; 982 select w.* 983 from w 984 where w.id in ( select x from q ) 985 ; 986 quit ; NOTE: PROCEDURE SQL used: real time 0.00 seconds

I guess too much of my experience is with sub queries in joins rather than nested sub queries.

Thanks. IanWhitlock@westat.com -----Original Message----- From: fernwood_pe@HOTMAIL.COM [mailto:fernwood_pe@HOTMAIL.COM] Sent: Tuesday, November 19, 2002 1:02 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: keep getting a warning message I don't know how to stop it

Ian, I am not sure where the blatant error is. From my experience a nested sub- query as is in:

409 proc sql; 410 update hold as new 411 set startDate = ( 412 select startDate 413 from hnew as dates 414 where dates.id = new.id ) 415 where id in (select id from hnew );

has access to the columns of both the inner table (dates.id) and the outer table (new.id). So the column startDate is legal and indeed the table is being updated, albeit with the value it already has. You would get an error if you had fully qualified startDate as dates.startDate

265 proc sql; 266 update hold as new 267 set startDate = ( 268 select dates.startDate 269 from hnew as dates 270 where dates.id = new.id ) 271 where id in (select id from hnew ); ERROR: Column startDate could not be found in the table/view identified with the correlation name DATES. ERROR: Unresolved reference to table/correlation name dates. ERROR: startDate, a numeric column, can only be updated with a numeric expression. 272 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used: real time 0.01 seconds cpu time 0.01 seconds

peter