topic update from one table to another in SAS Procedures

update from one table to another

podarum — Tue, 16 Jun 2015 20:35:41 GMT

Hi, I'm not that familiar with the update step in SAS. I have a code in Teradata that uses update, and I need to find the equivalent in proc sql.. However, I'm not sure if the way I have it set out is the most optimal way. In my Proc SQL, it's taking way to long to process.

Teradata SQl Code:

UPDATE ACCT_TABLE

FROM BAL_TABLE A

SET CURR_STRT_DT = SNAP_DT

WHERE ACCT_TABLE.ARQ_ID = A.ARQ_ID

AND ACCT_TABLE.OD_CURR_STRT_DT IS NULL

AND ACCT_TABLE.ACCT_OPEN_CLS_STS = 'O'

AND A.BAL_INCL_B < 0

;

PROC SQL (My)

proc sql;

update ACCT_TABLE B

set CURR_STRT_DT = (select SNAP_DT from BAL_TABLE A

WHERE A.ARQ_ID = B.ARQ_ID

AND B.SNAP_DT is NULL

AND B.ACCT_OPEN_CLS_STS = 'O'

AND A.BAL_INCL_B < 0)

;quit;

Re: update from one table to another

kiranv_ — Tue, 16 Jun 2015 23:16:20 GMT

If both tables are in Teradata, then explicit pass through would be good

Re: update from one table to another

podarum — Wed, 17 Jun 2015 00:13:17 GMT

The tables are originally from Teradata, but I was able to bring them locally into my SAs server, and I have to update them there. Using the second code.

Re: update from one table to another

kiranv_ — Wed, 17 Jun 2015 00:22:35 GMT

if you have both tables in Teradata, if possible please run an explain plan and check what it tell you. How large are this tables. if you both tables in Teradata, it is always a good practice to do it in Teradata