topic Re: Update a column in a table with values from another table in SAS Programming

Update a column in a table with values from another table

Sudhan — Tue, 05 Mar 2019 07:26:31 GMT

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.

Re: Update a column in a table with values from another table

PeterClemmensen — Tue, 05 Mar 2019 07:40:32 GMT

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.

Re: Update a column in a table with values from another table

RW9 — Tue, 05 Mar 2019 08:31:19 GMT

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-data-AKA-generate/ta-p/258712

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.

Re: Update a column in a table with values from another table

Kurt_Bremser — Tue, 05 Mar 2019 08:36:41 GMT

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.