Update columns in one table with values from another table - both table have the same structure,

[SasDewd]

Hello,

I have what I thought was a very simple and basic task to do.

I have 2 tables, T1 and T2, both of them with exact same table structure.

I'm just trying to update a few columns in T1 with values from T2 based on the common key column - ID.

Here's my code that appears to be working but that has been running for more than 2 hrs and it still hasn't finished.

PROC SQL;
UPDATE WORK.TableToBeUpdated AS T1
SET
Column1 = (SELECT Column1 FROM WORK.Newest AS T2 WHERE T1.ID = T2.ID)
, Column2 = (SELECT Column2 FROM WORK.Newest AS T2 WHERE T1.ID = T2.ID)
WHERE T1.ID IN (SELECT ID FROM WORK.Newest)
;
QUIT;

Both of the tables have ~200 columns and ~1mil records.

Since I'm used to working in SQL Server, I know this kind of task would take me about 20 secs to both write the command and run it but here either I'm using the wrong approach to solve this or SAS is executing this kind of code completely different.

Can you offer any ideas?

Thanks,

[Reeza]

Are all the tables in the same place? I suspect a join may be quicker here as well, since it's not quite a straight update (conditional) and you're essentially doing inline sql queries which will run twice on each row so will be slow.

[SasDewd]

Yes, both of the tables are in same library.

[Reeza]

Have you tried a data step update, or modify? Or do you have other columns you don't want to change?

[SasDewd]

This is the only thing I tried as I assumed it would be a very simple update statement.

Most of the columns I do not want to update just a few of them (about 20 out of 200).

So, let's say there are 2 tables:

Table1

ID                    First             Last                    Salary

1                      John           Hopkins               $3,000

2                      Jim              Stevens               $2,000

3                      Steve           Gordon               $1,000

Table2

ID                    First             Last                    Salary

3                     Steve          Gordon               $3,000

All I want to do is update Table1 and make sure that Steve's salary is $3,000, which is the value from Table2.

Thanks!

[Reeza]

Then I would probably say do a SQL JOIN instead and use coalesce to pick the data needed.

[SasDewd]

Thank you @Reeza ... I'll look into what you suggested.

My understanding is tha COALESCE returns first nonmissing value but in my case the values may not be missing, they're just not correct and need to be updated.

Anyway, thanks for your help!

[Reeza]

COALESCE would be used to pick the first value from the updated data set and if it doesn't exist (ID not in the table) to leave it alone, or use present value. You can get the same functionality with a CASE statement but COALESCE() will be easier IMO.

[ballardw]

---

@SasDewd wrote:

This is the only thing I tried as I assumed it would be a very simple update statement.

Most of the columns I do not want to update just a few of them (about 20 out of 200).

So, let's say there are 2 tables:

Table1

ID                    First             Last                    Salary

1                      John           Hopkins               $3,000

2                      Jim              Stevens               $2,000

3                      Steve           Gordon               $1,000

Table2

ID                    First             Last                    Salary

3                     Steve          Gordon               $3,000

All I want to do is update Table1 and make sure that Steve's salary is $3,000, which is the value from Table2.

Thanks!

---

Please note that your ID variable has different values for Steve in the two data sets. So your code based on matching ID is not going to work. You would need to join, in this case, on first and last. Or address the id values.

Dyslexic today. Sorry about that. Swore that I saw Id = 1 in the second set.

[SasDewd]

Thank you @ballardw !!

Hmm...both Steves have ID = 3  and all the other fields are the same except the Salary field.

Perhaps this was not a great example and I was just trying to explain that all I'm really trying to do is take Steve's Salary value from Table2 and assign it to Steve's Salary value in Table1.

There cannot be another Steve Gordon with different ID or anything like that.

If this was SQL Server he TSQL code below would have taken care of what I need and it would have finished in seconds:

UPDATE T1

SET    T1.Salary= T2.Salary

FROM   Table1 T1

INNER JOIN Table2 T2 ON T1.ID= T2.ID

So I'm just shocked that such a simple thing cannot be achieved in SAS without having to write lines of code and/or running it for hours.

Oh well, something new to learn I guess 🙂