Help using Base SAS procedures

multiple SET in PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

multiple SET in PROC SQL

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;

 

 


Accepted Solutions
Solution
‎10-10-2015 09:34 AM
Respected Advisor
Posts: 4,640

Re: multiple SET in PROC SQL

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)

PG

View solution in original post


All Replies
Solution
‎10-10-2015 09:34 AM
Respected Advisor
Posts: 4,640

Re: multiple SET in PROC SQL

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)

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 1595 views
  • 0 likes
  • 2 in conversation