topic Re: SQL SET..SELECT in SAS Procedures

SQL SET..SELECT

gamotte — Thu, 22 Nov 2012 11:43:02 GMT

Hi all,

I would like to update several columns in a table from their

counterparts in another table.

If I only wanted to update one variable I could do :

UPDATE T1

SET X = (

SELECT X

FROM T2

WHERE T2.ID=T1.ID

);

What if I also want to update variable Y ? Is there a way to achieve the result

with only one select ?

I tried the following with no luck :

UPDATE T1

SET (X,Y) = (

SELECT X, Y

FROM T2

WHERE T2.ID=T1.ID

);

Thanks

LinusH — Thu, 22 Nov 2012 13:05:43 GMT

I am usually a fan of SQL, but when it comes to update syntax, no...

To my knowledge, you need multiple SELECTs to do this.

So, I would suggest to use a data step update, using MODIFY. Should cover most scenarios of updating using a transaction table.

gamotte — Thu, 22 Nov 2012 13:31:17 GMT

OK, I reached the same conclusion by searching similar threads on other forums.

Thanks