topic Proc SQL Multiple Columns Update in SAS Procedures

Proc SQL Multiple Columns Update

Pablo — Thu, 17 Feb 2011 18:18:38 GMT

Hello Experts,

Is it possible to execute a multiple column update using proc sql?. Example Query:

update tableA T
set ( column1,
column2,
column3) = (select S.column1,
S.column2,
S.column3
from tableB S
where S.filter1 = T.Filter1)

This type of update can be executed in Oracle

Thanks for your help.

Pablo.

Re: Proc SQL Multiple Columns Update

DBailey — Thu, 17 Feb 2011 19:13:10 GMT

I believe you have to do it this way:

update tableA T
set column1 = (select S.column1 from tableB S where S.filter1 = T.Filter1),
column2 = (select S.column2 from tableB S where S.filter1 = T.Filter1),
column3 = (select S.column1 from tableB S where S.filter1 = T.Filter1)
where
t.filter1 in (select filter1 from tableb);

If you don't do the last criteria, then you will get null values populated when there is no match.

Re: Proc SQL Multiple Columns Update

Pablo — Thu, 17 Feb 2011 19:46:06 GMT

Thanks DBailey for your response.

Yes, I was aware of that method with SAS, but it is not an option for me at the moment. I just created a direct connection to Oracle so the update statement will be executed at the Database not at the SAS server and it is working ok.

It seems like SAS does not support vector style queries.

Thanks for your help.

Pablo.