topic Re: SAS Update fast process in SAS Programming

SAS Update fast process

sameer112217 — Sat, 25 May 2019 07:10:59 GMT

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;

Re: SAS Update fast process

ErikLund_Jensen — Sat, 25 May 2019 09:55:21 GMT

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.

Re: SAS Update fast process

sameer112217 — Wed, 29 May 2019 05:09:49 GMT

The update table is vw_SAS_CMDM_CI_General_Insurance

Re: SAS Update fast process

Patrick — Wed, 29 May 2019 07:08:24 GMT

@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;