topic Re: Updating 2 million table from another 2 mio table in SAS Programming

Updating 2 million table from another 2 mio table

dincooo — Wed, 02 Jan 2019 13:11:31 GMT

Hi experts,

I have a table named table_a with 2 million records. I want to update a column of table_a from another 2 millon rows table- named table_b.

My query is:

proc sql;
update table_a u
set BIRTH_PLACE=(select BIRTH_PLACE from table_b as n
where u.party_number=n.party_number)
where u.party_number in (select party_number from table_b);
quit;

This query takes too much time and never ending. Do you have any suggestion how to update this table?

Many thanks,

Onur

Re: Updating 2 million table from another 2 mio table

tomrvincent — Wed, 02 Jan 2019 13:40:13 GMT

Is table_a a sas dataset?

Re: Updating 2 million table from another 2 mio table

dincooo — Wed, 02 Jan 2019 13:42:08 GMT

These two datasets are Oracle tables and we have the right to see them at SAS EG.

Re: Updating 2 million table from another 2 mio table

tomrvincent — Wed, 02 Jan 2019 14:20:09 GMT

Then I'd suggest doing the update with a macro loop, one record at a time (1st step in the loop would be to pick minimum party number greater than previously selected party number). At least that would not be 'never ending'.

Re: Updating 2 million table from another 2 mio table

dincooo — Wed, 02 Jan 2019 15:06:11 GMT

Hi,

Thank you for the answer. If it is possible can you please write the code that does like this?

Re: Updating 2 million table from another 2 mio table

Shmuel — Wed, 02 Jan 2019 16:20:20 GMT

Alternatively -

1) Load both tables, ID and the variable to update, into sas;

2) Run update in SAS. If ID relation between tables is 1:1 or 1:N (not many to many)

then better use data step with merge or update staement.

It will be much faster then SQL.

3) Update Oracle table from sas result table.

You can use obs and firstobs option2 to manage update part by part.

(step 1: obs=1000; step 2: firstobs=1001 obs=1000, step 3: obs=2001 obs=1000, etc.)

Re: Updating 2 million table from another 2 mio table

dincooo — Thu, 03 Jan 2019 06:47:06 GMT

Hi,

Thank you for your comment can you please write the code that does the last step?

Many thanks

Re: Updating 2 million table from another 2 mio table

Shmuel — Thu, 03 Jan 2019 15:46:00 GMT

I have no oracle environment, now, to check my code.

Here is a skeleton to adapt to your environment and needs.

Code should run on sas with access to oracle:

/* step 1 - define connection parameters */
LIBNAME DBCON ORACLE USER=&UserNm PASS=&PassWd PATH='EDWP' SCHEMA=IDWE CONNECTION=GLOBAL;

/* step 2 - load tables from oracle, relevant variables only */
PROC SQL;
	connect using DBCON;
        select * from connection to oracle
            (create table1 as
                select party_number, birth_place from table_a);
            (create table2 as
                select party_number, birth_place from table_b);
    disconnect from oracle; 
quit;

/* sort tables in sas, prepared to create updated table3 */

proc sort data=table1; by party_number; run;
proc sort data=table2; by party_number; run;

data table3; 
  merge table1(in=in1 rename=(birth_date=bd))
             table2(in=in2);  
    by party_number;
         /* keep observation only those in both tables and date differ */
         if in1 and in2 and birth_date ne bd;
         drop bd;
run;

/* Check table3 is birth_date updated as desired !!! */
/* check log - is updated observations count logic */

PROC SQL;
	connect using DBCON;
	select *  from connection to oracle   	
            (update table_a as a   from table3 as n
              set a.BIRTH_PLACE=n.birth_date
              where a.party_number = n.party_number
             );

    disconnect from oracle; 
quit;

By keeping only those observations in both tables and with different birth dates,

you eliminate number of observations to update and save time.

NOTE - check table3 before trying update oracle table.

Continue only if you accept the results.