SAS Update fast process

[sameer112217]

I am using an update process in SAS which is an actual SQL query. It is processing very fast in SQL but in SAS, the update process is take a long time. I need help as to how I can I optimize the update sub query from another table

Proc SQL;
update vw_SAS_CMDM_CI_General_Insurance
set SumInsured=z.SUM_INSURED ,
TotalPremium=z.NET_PREMIUM
from (select POLICYNO,sum(NET_PREMIUM)NET_PREMIUM,sum(SUM_INSURED)SUM_INSURED from DPR.pr_ytd with(nolock) where
system_id not in ('manual','RI','manual_adv','manual_adv_reversal') group by policyno ) Z
where z.POLICYNO =vw_SAS_CMDM_CI_General_Insurance.PolicyNo;

quit;

[ErikLund_Jensen]

Hi @sameer112217 

Where is your update table, and the contributing tables? - In your SAS code you have a one-level table name vw_SAS_CMDM_CI_General_Insurance, and a one-level name is a short notation for work.tablename, which means that you update a temporary SAS table in the work library. It does not really make sense, so I think you are half way through converting native SQL code to SAS.

If you can process the code in the database, then all referenced tables must exist in the data base, and in that case you are better off with an explicit pass-thru, where the code is sent from SAS and executed in the database. Then you can use your working code without changes. 

We need more information to help you.

[sameer112217]

The update table is vw_SAS_CMDM_CI_General_Insurance

[Patrick]

@sameer112217 

vw_SAS_CMDM_CI_General_Insurance is a one level name meaning it is a SAS WORK table. 

DPR.pr_ytd with(nolock) is a two level name and the nolock option indicates that this is a SQL Server table.

I guess when you've tested in SQL Server directly then the one level table was also a SQL Server table. But now you're combining data from two different sources which likely leads to a lot of costly data movement which significant impact on performance.

If above is true then also use a two level name for your vw_,,, table and keep all the processing in the database.

To get a bit more info of what's going on eventually also add the following global options and two options to the SQL statement and also post the SAS log.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql feedback _method;
<your working and tested code >
quit;