topic Re: Update table in SAS Procedures

Update table

SASEnthusiast — Sun, 06 Mar 2016 07:33:10 GMT

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

like:

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

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 a.network, 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  a.network, 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))

		;
	quit;

Re: Update table

LinusH — Sun, 06 Mar 2016 12:39:38 GMT

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.

Re: Update table

FreelanceReinh — Sun, 06 Mar 2016 13:12:34 GMT

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.

Example:

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

data table2;
input id x y z;
cards;
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 t1.id=t2.id),
    y=(select y from table2 t2 where t1.id=t2.id)
where id in (select id from table2);
quit;

proc print data=table1;
run;

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 t1.id=t2.id), x),
    y=coalesce((select y from table2 t2 where t1.id=t2.id), y);
quit;

(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
       table2;
by id;
where 1<=id<=4;
run;

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.

Re: Update table

RW9 — Mon, 07 Mar 2016 10:07:20 GMT

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.