multiple SET in PROC SQL

[buckeyefisher]

I am trying to update multiple columns using SQL update. It gives me error at highlighted and undelined area (at the end of the where clause)

proc sql;
update want as a
set CntA = (select coalesce(sum(A),0)
, CntB = (select coalesce(sum(B),0)
, CntC = (select coalesce(sum(C),0)
, CntD = (select coalesce(sum(D),0)
from have where (a.Originator = Originator) and (year) between (a.year)-8 and (a.failureyear)-1;
quit;

[PGStats · Accepted solution]

Unfortunately, the SQL UPDATE syntax is quite rigid. You must repeat the subquery on each assignment

proc sql;
update want as a 
set 
  CntA = (select coalesce(sum(A),0) 
	from have 
	where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1)
, CntB = (select coalesce(sum(B),0) 
	from have 
	where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1)
, CntC = (select coalesce(sum(C),0) 
	from have 
	where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1)
, CntD = (select coalesce(sum(D),0) 
	from have 
	where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1);
quit;

(untested)