Help using Base SAS procedures

Proc SQL Multiple Columns Update

Reply
New Contributor
Posts: 2

Proc SQL Multiple Columns Update

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.
Super Contributor
Posts: 578

Re: Proc SQL Multiple Columns Update

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.
New Contributor
Posts: 2

Re: Proc SQL Multiple Columns Update

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.
Ask a Question
Discussion stats
  • 2 replies
  • 423 views
  • 0 likes
  • 2 in conversation