Hi All,
Basically we have table loader code from SAS DI which looks as below. We are using Update Insert technique in DI and hence below is the code which is used. However, it is taking whole lot of time. sasdata is sas library. And we have indexes on both the tables.
sasdata.TBL_DTLS_ERROR_APD has 212354 records and etls_lastTable has 60000 records.
I am out of all my ideas on how to reduce this time of execution. Any help is really appreciated.
data sasdata.TBL_DTLS_ERROR_APD;
length etls_msg $200;
drop etls_msg;
modify sasdata.TBL_DTLS_ERROR_APD
&etls_lastTable
updatemode = nomissingcheck
;
by POL_NUM_TXT INSURED_ID;
/* if the record does not exist in the master, then add it */
if %sysrc(_DSENMR) eq _iorc_ then
output;
/* if the record exists in the master, then replace it */
else if %sysrc(_SOK) eq _iorc_ then
replace;
%if &etls_StopOnIORC ne %then
%do;
if _iorc_ in (&etls_StopOnIORC) then
do;
etls_msg = iorcmsg();
put etls_msg;
%rcSetDS(5)
stop;
end;
%end;
_iorc_ = 0;
_error_ = 0;
run;
What's the actual run time for this? While you may think it's long it may be "normal". Not saying it can't be improved but it's a matter of how much more could you potentially improve it from where it's at. Some code can be cut in half on execution time, while others extensive changes and/or re-writing it might only improve by milliseconds.
A couple of ideas on how to improve performance:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: