topic Re: Updating variable in huge table A with values from table B in SAS Programming

Updating variable in huge table A with values from table B

EinarRoed — Thu, 11 Aug 2022 06:44:42 GMT

We've got a series of large tables in PostgreSQL (over 500 million rows in some). Each table has an attribute called REF_ID, and one called OBJECT_CD. Both are varchar(32). OBJECT_CD is always NULL, but now we need to give it a value (on all rows). Let's call one of these large tables T1.

We've also got the table T2. It's a smaller table that contains 1 row per REF_ID. The attribute OBJECT_CD is always filled out here.

I want to update T1.OBJECT_CD with values from T2.OBJECT_CD (where T1.REF_ID = T2.REF_ID).

Sounds simple, but I have a hard time finding an effective way to do it. The PostgreSQL tables are so large, and the code I've tried runs "forever" and times out.

An example of something I've tried:

proc sql;
	update pgsql.T1
	set OBJECT_CD = (select distinct OBJECT_CD
		from T2
		where T1.REF_ID = T2.REF_ID);
quit;

Any advice on the most effective way to update huge PostgreSQL tables?

Re: Updating variable in huge table A with values from table B

andreas_lds — Thu, 11 Aug 2022 06:50:53 GMT

Is t2 a sas dataset or another postgres table?

Re: Updating variable in huge table A with values from table B

EinarRoed — Thu, 11 Aug 2022 07:16:03 GMT

T2 is a SAS dataset.

Re: Updating variable in huge table A with values from table B

PeterClemmensen — Thu, 11 Aug 2022 07:16:28 GMT

Try loading it to the DB and do the entire update there.

Re: Updating variable in huge table A with values from table B

Patrick — Thu, 11 Aug 2022 08:47:58 GMT

As others suggested load the small table into Postgres and then execute everything in-database. Code close to below should work.

%let pg_libref=pgsql;
%let pg_schema=;
proc sql noprint;
  select sysvalue into :pg_schema trimmed
  from dictionary.libnames
  where
    libname="%upcase(&pg_libref)"
    and sysname='Schema/Owner'
  ;
quit;

proc datasets lib=&pg_libref nolist nowarn;
  delete T2;
run;quit;

proc append data=work.t2 base=&pg_libref..t2;
run;quit;

proc sql;
  connect using &pg_libref as pg;
  execute
  (
	update &pg_schema..T1
  	set OBJECT_CD = (  select OBJECT_CD
                  		from &pg_schema..T2
  		                where T1.REF_ID = T2.REF_ID)
    ;
  ) by pg;
  disconnect from pg;
quit;

proc datasets lib=&pg_libref nolist nowarn;
  delete T2;
run;quit;

Re: Updating variable in huge table A with values from table B

Patrick — Thu, 11 Aug 2022 08:49:42 GMT

@EinarRoed I've added a few obvious fixes to the post you already accepted as solution. Still totally untested code of course but I've used something similar in a real implementation where things work.