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

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

SasDewd — Thu, 13 Jun 2019 18:46:24 GMT

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,

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

Reeza — Thu, 13 Jun 2019 19:34:56 GMT

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.

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

SasDewd — Thu, 13 Jun 2019 19:38:51 GMT

Yes, both of the tables are in same library.

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

Reeza — Thu, 13 Jun 2019 19:40:44 GMT

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

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

SasDewd — Thu, 13 Jun 2019 19:48:28 GMT

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!

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

Reeza — Thu, 13 Jun 2019 19:49:24 GMT

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

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

SasDewd — Thu, 13 Jun 2019 19:55:47 GMT

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!

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

Reeza — Thu, 13 Jun 2019 20:03:27 GMT

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.

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

ballardw — Thu, 13 Jun 2019 21:26:11 GMT

@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.

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

SasDewd — Thu, 13 Jun 2019 20:27:41 GMT

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 🙂