Date:         Fri, 30 Dec 2005 14:27:07 -0500
Reply-To:     "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Subject:      Re: SQL update multiple columns
Comments: To: sas-l@uga.edu

saslist wrote:
> Is it possible to use proc sql to update multiple columns at the same
> time. Oracle supports the following:
>
> update table1 t1
> set (ti.id, t1.var1, t1.var2, t1var3) =
>     (select ti.id, t1.var1, t1.var2, t1var3 from
>     table2 where id=100 )
> where id=100
>
> Is such a thing possible is SAS?

No.  Proc SQL does not support 'vector style' at present.  Each column has
to be set independently.
Be sure to critique features  or make suggestions and at suggest@sas.com

data table1;
  id=100;
  var1=1;
  var2=2;
  var3=3;
run;

data table2;
  set t1;
  var1+100;
  var2+100;
  var3+100;
run;

proc sql;
update table1 t1
set
  var1 = (select var1 from table2 where id=100)
, var2 = (select var2 from table2 where id=100)
, var3 = (select var3 from table2 where id=100)
where
  id = 100
  ;
quit;


--
Richard A. DeVenezia
http://www.devenezia.com/