topic Re: SQL Update - Updating a table with corresponding values in a second table in SAS Programming

SQL Update - Updating a table with corresponding values in a second table

FatCaptain — Thu, 03 Mar 2011 13:38:09 GMT

I have 2 tables and I'm wanting to update the values in the first with the corresponding values in the second, e.g.

table1
a 1
b 2
c 3
d 4

table2
b 5
c 6

to result in;

table1
a 1
b 5
c 6
d 4

I'm not that familiar with SAS SQL syntax and the following code I wrote is displaying an error.

PROC SQL ;
UPDATE
table1 AS a
LEFT JOIN
table2 AS b
ON
a.var1 EQ b.var1
SET
a.var2 = b.var2
;
QUIT ;

65 LEFT JOIN
----
79
76
ERROR 79-322: Expecting a SET.

ERROR 76-322: Syntax error, statement will be ignored.

Does SAS SQL support this kind of updating?

As always, any help with this is greatly appreciated.

Cheers,

Fat Captain.

Re: SQL Update - Updating a table with corresponding values in a second table

Oleg_L — Thu, 03 Mar 2011 14:29:31 GMT

One of many ways.

[pre]

data table1;
infile cards;
input var1 $ var2;
cards;
a 1
b 2
c 3
d 4
;
data table2;
infile cards;
input var1 $ var2;
cards;
b 5
c 6
;
proc sql noprint;
create table table1 as select a.var1, coalesce(b.var2,a.var2) as var2
from table1 as a left join table2 as b on a.var1=b.var1;
quit;
[/pre]

Re: SQL Update - Updating a table with corresponding values in a second table

SAS83 — Thu, 03 Mar 2011 14:37:49 GMT

Another way.. Make sure to sort the two tables before merge .. but for this case they are already sorted so no sorting is done..

data table1;
infile cards;
input var1 $ var2 @@;
cards;
a 1 b 2 c 3 d 4
;
data table2;
infile cards;
input var1 $ var2 @@;
cards;
b 5 c 6
;

data x;
merge table1 table2;
by var1;
run;

Re: SQL Update - Updating a table with corresponding values in a second table

SAS83 — Thu, 03 Mar 2011 14:43:43 GMT

Make sure Table1 is first then Table2 in the merge statement.. if you dont use ina and inb....

data table1;
infile cards;
input var1 $ var2 @@;
cards;
a 1 b 2 c 3 d 4
;
data table2;
infile cards;
input var1 $ var2 @@;
cards;
b 5 c 6
;

data x;
merge table1 (in=ina) table2 (in=inb);
by var1;
if ina;
run;

this is similar to SQL left join..

Re: SQL Update - Updating a table with corresponding values in a second table

Reeza — Fri, 04 Mar 2011 00:35:14 GMT

Here's a sql solution since all the other are datastep and its what you were looking for initially. I doubt its more efficient, but useful to know.

[pre]
data have1;
input var1 $1 var2;
datalines;
a 1
b 2
c 3
d 4
;
run;

data have2;
input var1 $1 var2;
datalines;
b 5
c 6
;
run;

proc sql;
update have1 as h1
set var2=(select var2 from have2 as h2 where h1.var1=h2.var1)
where var1 in (select var1 from have2);
quit;
[/pre]

Re: SQL Update - Updating a table with corresponding values in a second table

FatCaptain — Fri, 04 Mar 2011 11:25:47 GMT

Thank you all for your responses.

Cheers,

Fat Captain.

Re: SQL Update - Updating a table with corresponding values in a second table

PhilFromGER — Mon, 31 Oct 2016 15:57:02 GMT

Reeza provides the ideal solution, eliminating the need to create a new data set.

proc sql;
 update have1 as h1
 set var2=(select var2 from have2 as h2 where h1.var1=h2.var1)
 where var1 in (select var1 from have2);
 quit;

I reckon the where statement only improves performance. If you're updating only a few values in a very large data set then this will significantly speed up the update. Definitely my favorite solution to the problem.