Prev: Missing value with e8601dt. format
Next: New book announcement: Discovering Statistics Using SAS by Andy Field & Jeremy Miles
From: Kevin Myers on 24 Nov 2009 15:45
Using SQL in SAS 8.2, I would like to update the contents of specific =
rows in one table using rows with matching composite key column values =
from another table. In other SQL dialects, this could be accomplished =
using code similar to the following:

/* syntax 1 (Microsoft SQL Server) */
update table1
set table1.column1=3Dtable2.column1, table1.column2=3Dtable2.column2
from table2 where table1.key1=3Dtable2.key1 and =
table1.key2=3Dtable2.key2;

/* syntax 2 (Microsoft Access, MySQL) */
update table1, table2
set table1.column1=3Dtable2.column1, table1.column2=3Dtable2.column2
where table1.key1=3Dtable2.key1 and table1.key2=3Dtable2.key2;

/* syntax 3 */
update table1
set (table1.column1, table1.column2)=3D(select column1, column2 from =
table2 where table2.key1=3Dtable1.key1 and =
table2.key2.key2=3Dtable1.key2);

Unfortunately, SAS does not appear to support syntax comparable to any =
of the above options for the UPDATE statement of PROC SQL. Does anyone =
have any suggestions as to how this can be accomplished using PROC SQL =
in SAS with any code that remains reasonably compact and efficient?

Couple of additional notes/restrictions:
1. The actual tables may reside in an external database for which the =
specific DBMS is not necessarily known ahead of time.
2. It may not be possible to add any other tables or views to the =
external database.

Note that I am familiar with comparable update capabilities of the data =
step (using the MODIFY statement, etc.), but a data step based solution =
is undesirable for my specific situation.

Thanks,
Kevin M.
 | 
Pages: 1
Prev: Missing value with e8601dt. format
Next: New book announcement: Discovering Statistics Using SAS by Andy Field & Jeremy Miles