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.
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.
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.
Post test data of both datasets in the form of a datastep:
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: