Update a column in a table with values from another table

[Sudhan]

Hi,

As part of my programming, I have a query which updates a TARGET table with values from the SOURCE table.

proc sql;
update SCHEMA.TARGET as u
set &COLUMN_NAME. =(select value from &COLUMN_NAME. as n
where strip(u.ID)=strip(n.ID))
;quit;

COLUMN_NAME is an attribute on TARGET table, it is a table as well. I am trying to update the column with values from the corresponding table. The variable values will be changed dynamically.

Can you please advise how to achieve this using data steps, this approach seems to be a bit slow.

Thank you.

[PeterClemmensen]

What do you mean by "COLUMN_NAME is an attribute on TARGET table"?

Also, can you provide some sample data for us to work with, that resembles your actual data? Makes it much easier to help you.

[RW9]

Post test data of both datasets in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And show what you expect from them.

I am pretty certain however that there is a much better method than the update you present here, a simple merge on ID would be far more efficient.

[Kurt_Bremser · Accepted solution]

proc sort data=&column_name.;
by id;
run;

proc sort data=schema.target;
by id;
run;

data schema.target_new;
merge
  schema.target (in=orig)
  &column_name. (in=update keep=id value)
;
by id;
if orig;
if update then &column_name. = value;
drop value;
run;

Once you have verified the result, remove schema.target and rename schema.target_new to schema.target.