Efficiently updating Sql server Table using SAS Dataset

[pchegoor]

Hello All,

I am updating a SQL server Table called sml_emp_exp_rpt (Master) using a SAS
Dataset called all_renewals (Transaction).Both the Master and Transaction tables have

fields grpmain and rnwl_dt.

The Master table can have more than one record for the combination of
grpmain and rnwl_dt  fields from Transaction table and all such records need to be
updated in the master Table using the Information from transaction table.The Transaction table
has only one record for a grpmain and rnwl_dt combination.Also all
combinations of grpmain and rnwl_dt in the Master table are also present in
the Transaction table.

So i am using the below code to update 4 columns in the Master table using
the transaction table.This method works ,however is very slow and right now
takes about 35 mins to update 133K records in the Master table.

Here is my SAS Code:

/*********************************************************************************************************/

libname sql_sgr SQLSVR user=user1 password=pwd1 datasrc=testpath;
libname sm_grp '/shrproj/files/sas_data/sm_grp';

proc sql;
create table all_renewals as
Select    distinct grpmain,
             datepart(rnwl_dt) as rnwl_dt,
             agency,
             agency_agent,
             mkt_rep,
             rnwl_status_cd
from sm_grp.be_ALL_RENEWALS
;
quit;

proc sql;
update sql_sgr.sml_emp_exp_rpt a
set agency=(select b.agency
                   from all_renewals as b
                  where a.rnwl_dt=b.rnwl_dt
                  and a.grpmain=b.grpmain),
  agency_agent=(select b.agency_agent
                   from all_renewals as b
                 where a.rnwl_dt=b.rnwl_dt
                 and a.grpmain=b.grpmain),
  MRKT_REP_ID=(select b.mkt_rep
                 from all_renewals as b
                where a.rnwl_dt=b.rnwl_dt
                and a.grpmain=b.grpmain),
  rnwl_status_cd=(select b.rnwl_status_cd
                 from all_renewals as b
               where a.rnwl_dt=b.rnwl_dt
               and a.grpmain=b.grpmain)
;
quit

/***************************************************************************************************/

My question is how can the above Code be modified to Improve the Efficiency
of the Update?

Please let me know of any Solution.

Thanks

[LinusH]

I like SQL a lot, but for certain kind of updates, it's terrible...

How large is your Master table?

If you have the appropriate rights, it could probably be more efficient to extract the whole table to SAS, update it using  a data step, truncate the original in SQL Server, and bulk load it back.

Another option is to upload you transactions as a temporary tabel to SQL Server, and then perhaps you'll get a implicit SQL pass-thru of the update operations (use options sastrace=',,,d' sastraceloc=saslog; to verify), or use explicit SQL pass-thru to make sure that all update processing will take place in SQL Server.

[DBailey · Accepted solution]

Agree w/ LinusH.  Create an "input" table then merge the two.  You might include the insertbuff option as well to speed up the inserts.  Bulk loading might be a possibility but I've never been able to get it to work for Sqlserver.

libname sql_sgr SQLSVR user=user1 password=pwd1 datasrc=testpath insertbuff = 10000;

libname sm_grp '/shrproj/files/sas_data/sm_grp';

proc sql;

connect to SQLSVR as cSQL (user=user1 password=pwd1 datasrc=testpath insertbuff = 10000);

create table sql_sgr.tmp as

Select    distinct grpmain,

             datepart(rnwl_dt) as rnwl_dt,

             agency,

             agency_agent,

             mkt_rep,

             rnwl_status_cd

from sm_grp.be_ALL_RENEWALS

execute (

     merge into sml_emp_exp_rpt a

     using tmp b

          on (a.rnwl_dt=b.rnwl_dt

                  and a.grpmain=b.grpmain)

     when matched then update

          set

               a.agency=b.agency,

               a.agency_agent=b.agency_agent,

               a.mkt_rep=b.mkt_rep,

               a.mwl_status_cd=b.mwl_status_cd

) by cSQL;

execute (drop table tmp) by cSQL;

disconnect from cSQL;

quit;

check merge statement syntax

[pchegoor]

Thanks LinusH and DBailey  i used the above suggested method and it worked. This is what i did :

proc sql;

connect to SQLSVR (user=&sql_user_id. password="&sql_password." datasrc=&sql_path. insertbuff = 10000);

create table sql_sgr.all_renewals as

Select    distinct grpmain,

datepart(rnwl_dt) as rnwl_dt format=date9.,

agency,

agency_agent,

mkt_rep,

rnwl_status_cd

from sm_grp.be_ALL_RENEWALS;

execute (

     merge into sml_emp_exp_rpt a

     using all_renewals b

          on (a.rnwl_dt=b.rnwl_dt

                  and a.grpmain=b.grpmain)

     when matched then update

          set

               a.agency=b.agency,

               a.agency_agent=b.agency_agent,

               a.mrkt_rep_id=b.mkt_rep,

               a.rnwl_status_cd=b.rnwl_status_cd;

) by SQLSVR;

execute (drop table all_renewals) by SQLSVR;

disconnect from SQLSVR;

quit;

Now it takes only about 5 secs to update 135559  rows of the sql server table.

[sasuser77]

is it specific to SQL Server. I am having difficult making it work on Access database. 

ERROR: Execute: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.

[SASKiwi]

MS Access SQL is different to SQL Server SQL, Search the SAS Support site for examples of updating MS Access. If you are still stuck, create a new post. Adding to an existing post for a different problem is unlikely to be helpful.