I am trying to update a table in proc sql, to set our columns = to a temporary work tables values.
The below code is not working and giving me this error:
ERROR: Unresolved reference to table/correlation name transaction.
set col1 = (select col1 from table2 where table1.txn = txn)
where txn in (select txn from table2);
I am trying to avoid a datastep update statement, as it is recreating our table and seems to be a long time for a job that should be very simple.
Thanks in advance,
Well, firstly, I am not sure that an update statement like that would be quicker than a datastep. Your datastep could be taking a long time for a number of reasons - where is it located network/locally, how many observations/columns etc.
For your update, have you tried putting the Alias in the step:
proc sql; update table1 table1 /* Second one here is the alias */ set col1 = (select col1 from table2 where table1.txn = txn) where txn in (select txn from table2); quit;
However I am not sure your logic is right at all. The where clause will select multiple rows if they exist. Post example test data, and what you want the output to look like and we can suggest code.
Thanks for reply,
Our current code is in a datastep. The table is indexed, and it does not maintain indexes if we perform like we do below
So we are updating 2 columns on table1, which are both on table2. This step is recreating the table each time and it seems like an inefficient way to update a table.
Here's some sample data of the two columns that should be updating, also the column they are updating by:txn
Table1 (Table to update)
txn flag date
1021912 X 12/02/2015
Table2(updating table one data with flag and date) flag is empty
txn flag date
Note: Txn is a unique index on both tables.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.