BookmarkSubscribeRSS Feed
Obsidian | Level 7




 Hi, Is there a way to update multiple columns using Set statement.


Update table1 set (a,b,c,d,e,f,g,h,i,j,k)=
from table2 t2

 I am attaching my code below. I am not sure it would be relevant or not. This definetly gives a lot of Errors.


	proc sql;
		update inlib.Video_News
		set network= T2.Network, VideoFormat=VideoFormat, DistnChannel= DistnChannel, Platform= Platform, Quarter=T2.Quarter, Budget=T2.Budget_Capacity11
		from (select, b.Videoformat, b.distnchannel, b.Platform, a.Quarter, a.device, (budget* Capacity_pct) as Budget_Capacity11 
			from inlib.Video_News a
				left join (select   c.*, (Budget_Capacity/Capacity_tot) as Capacity_Pct  
							from inlib.Cap_News c
								left join (	select Quarter, Network, distnChannel, VideoFormat, Platform,device, sum(Budget_Capacity) as Capacity_Tot
											from inlib.Cap_News
											where upcase(device)= 'OTT'
											group by Quarter, network, device)d
							on upcase(c.Quarter)= upcase(d.Quarter)
							and upcase(c.Network)= upcase(d.Network)
							and upcase(c.VideoFormat)= upcase(d.VideoFormat)
							and upcase(c.Distnchannel)= upcase(d.Distnchannel)
							and upcase(c.Platform)= upcase(d.Platform) 
							and upcase(c.Device)= upcase(d.Device) 
							where upcase(c.device)= 'OTT') b			

	on compress(upcase(a.device)) = compress(upcase(b.Device)) 
	and compress(upcase(a.Network))= compress(upcase(b.Network)) 
	and compress(upcase(a.Quarter))=compress(upcase(b.Quarter))
	order by, b.Videoformat,b.distnchannel,  b.Platform,a.Quarter, a.device) T2

	where compress(upcase(device)) = compress(upcase(T2.Device)) 
	and compress(upcase(Network))= compress(upcase(T2.Network)) 
	and compress(upcase(Quarter))=compress(upcase(T2.Quarter))




Tourmaline | Level 20
No, I don't think that there are any similar SQL syntax for this. That's why I'm most of the time favour data step with modify by. It's simpler to code, and usually faster.
Data never sleeps
Jade | Level 19

Hi @SASEnthusiast,


You can update multiple columns using the SET clause of PROC SQL's UPDATE statement, but the syntax for this is different. If you compare your code to the official syntax of the UPDATE statement, you will notice that a FROM clause is not mentioned there. The "sql-expressions" in the syntax, however, are allowed to be query expressions, which in turn would involve FROM clauses.



data table1;
input id x y w;
1 11 111 1111
2 22 222 2222
3 33 333 3333
4 44 444 4444

data table2;
input id x y z;
2 20 200 2000
4 40 400 4000 
6 60 600 6000 

proc sql;
update table1 t1
set x=(select x from table2 t2 where,
    y=(select y from table2 t2 where
where id in (select id from table2);

proc print data=table1;

The above code updates TABLE1 in place. The subqueries select x from ... and select y from ... result in single values (which is important). Thus, the existing values of variables X and Y in TABLE1 are overwritten with the corresponding values from TABLE2 for the same ID. The WHERE clause (where id in ...) prevents that values from observations 1 and 3 are overwritten with missing values. Alternatively, this could be achieved by using the COALESCE function:

proc sql;
update table1 t1
set x=coalesce((select x from table2 t2 where, x),
    y=coalesce((select y from table2 t2 where, y);

(This would no longer work if some values selected from TABLE2 were missing values and the intention was to let them replace non-missing values in TABLE1.)


As mentioned by @LinusH, you could obtain the same result with a data step using the MODIFY statement:

data table1;
modify table1
by id;
where 1<=id<=4;

Without the WHERE statement, SAS would complain about ID 6 in TABLE2, which is not present in TABLE1. So, it would be better to restrict TABLE2 first to those IDs whose values are to be updated in TABLE1.


Diamond | Level 26 RW9
Diamond | Level 26

Rather than a big SQL statement, think about it in terms of Base SAS.  I.e. extract the data you need into SAS datasets, then merge them together.  It will help you debug it to as you will see the interveening steps.  So create a dataste of the inner joined data, then from that create another dataset which is summarised.  Finally merge the result to the main data.  Whilst one step may seem the best approach, SQL can be heavy on resource, datastep has a good chance of being quicker.


Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4 in conversation