Updating a table with data elements from multiple tables

[J_Yetman]

SAS newbie here.   I'm trying to update a table with data from multiple other tables.  I've tried to do it through a proc SQL statement as I'm much more familiar with SQL syntax than SAS data steps, but the proc SQL statement is either running very slowly (there are millions of records being updated in the query) or just not working in SAS.  So I'm hoping to find something much more efficient.

My Proc SQL update code looks like this right now:

update work.table_1 a

SET field_2 = (select field_2 from table_2 b

            where a.key_1 = b.key_1

               and b.year_db = '2019'

               and b.period_val = '5'),

        field_3 = (select field_3 from table_3 c

            where a.key_1 = c.key_1

               and c.year_db = '2019'

               and c.period_val = '5');

[tsap · Accepted solution]

Here is a possible alternative to the logic that you are currently using. One thing I will note is that I had to make a lot of assumptions to put this logic together, so it may not generate the intended output you needed. For example, I don't know whether field_2 and field_3 already exist on Table_1 or not. And if it does exist, whether you want the value to update with the values from table_2 and table_3 every time, or only when the value on table_2 or table_3 is not null, otherwise keep the value on table_1 for these fields. There are a couple of other unknowns that I assumed in creating this logic.

If this doesn't accomplish what you need, please submit logic to create small dummy datasets to mimic your table_1, table_2, and table_3... and a rough mockup of what the output should look like. That will give us the best idea of what the logic needs to look like to accomplish your request.

PROC SQL;
CREATE TABLE WORK.New_Table	AS
	SELECT
		  a.*
		, b.field_2
		, c.field_3

	FROM 	  Table_1		AS a
	LEFT JOIN Table_2		AS b	ON 	a.key_1 = b.key_1
									AND b.year_db = '2019'
									AND b.period_val = '5'
	LEFT JOIN Table_3		AS c	ON  a.key_1 = c.key_1
									AND c.year_db = '2019'
									AND c.period_val = '5';
QUIT;

If you need the final produced table to be named table_1 you can certainly change what I have (new_table), or you can do a datastep afterwards to rename the table, etc.

hope this helps.