BookmarkSubscribeRSS Feed
Bjs09f
Calcite | Level 5

Hi All,

 

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.

 

 

proc sql;

update table1

set col1 = (select col1 from table2 where table1.txn = txn)

where txn in (select txn from table2);

quit;

 

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,

 

Ben

 

6 REPLIES 6
mohamed_zaki
Barite | Level 11

Are you sure that the column txn exists in table1 and table2?

 

Also, what you trying to do exactly?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

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.

LinusH
Tourmaline | Level 20
May I that in many cases SQL is quite inefficient when it comes to updates.
On an indexed data set, a data step with modify by can be very fast - it does not recreate the data set, it updates it in place.
Data never sleeps
Bjs09f
Calcite | Level 5

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

 

data table1;

update table1

table2

UPDATEMODE=NOMISSINGCHECK;

by transaction_id;

run;

 

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.

 

Bjs09f
Calcite | Level 5

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

1021912                      2/24/2016

 

Note: Txn is a unique index on both tables.

LinusH
Tourmaline | Level 20
As stated, modify by will update in place which includes maintaining indexes.
Data never sleeps

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1308 views
  • 0 likes
  • 4 in conversation