multiple SET in PROC SQL

buckeyefisher — Thu, 08 Oct 2015 22:18:11 GMT

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;

Re: multiple SET in PROC SQL

PGStats — Fri, 09 Oct 2015 02:31:29 GMT

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)

topic Re: multiple SET in PROC SQL in SAS Procedures

multiple SET in PROC SQL

Re: multiple SET in PROC SQL