Hi Everyone,
Please i have an issue with my code.
im trying to create a table with 20 mill row reading from oracle data base.
however i get the below log error
proc sql;
374 create table soap_detl1 as
375 select a.ssnbr ,a.indnbr ,a.contnbr ,a.item ,a.qtycl ,a.qtyapp ,a.totcl ,a.totapp ,a.paid_amt ,a.exceedlimd
375 ! ,a.netamt ,a.prov_net_amt ,a.totrcvry
376 ,a.discd ,a.hasitems ,a.covnetamt ,a.treatperiod ,a.penaltyd ,a.reqnum ,a.untprice ,a.QTY_TYPE ,a.discount
376 ! ,a.pa_flg ,a.mnsrv
377 from &OPERATION..soapdetl_up a inner join ssnbr_list b
378 on a.ssnbr=b.ssnbr ;
ERROR: Error fetching from cursor. ORACLE error is ORA-01555: snapshot too old: rollback segment number 88 with name
"_SYSSMU88_4158259342$" too smallORA-02063: preceding line from STBDBLNK.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
379 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 59:35.10
cpu time 2:26.56
Appreciate your support
Thank you,
Ahmed Maher
The easiest way to speed your query up is to upload the SSNBR values you are interested in to a temp Oracle table, and perform the inner join in Oracle.
This is an internal Oracle issue, see here: https://www.tekstream.com/resource-center/ora-01555-snapshot-too-old/
I don't think you will be able to solve this from the SAS side.
The problem happens when the table you access from SAS is updated while you use it, and Oracle tries to provide you with a consistent state, rolling back the changes that are made simultaneously. When it runs out of resources to do this, you get 01555.
I don't know if ACCESS to Oracle provides an option to prevent the use of a snapshot and allow inconsistent data.
Since your query takes almost an hour and is just a subset based on a SAS WORK dataset, you need to speed that up.
How large is WORK.SSNBR_LIST?
This will only work for a limited number of items, as both the size of a macro variable (64K) and the size of a SQL statement have limits.
Simple proof of concept:
data names;
input name $;
datalines;
Alfred
William
;
proc sql noprint;
select quote(name) into :list separated by ","
from names;
quit;
proc sql;
select * from sashelp.class
where name in (&list.);
quit;
Alternatively, you can write the SQL code to a file (where you can have a lot more items in the IN list) and then %include that:
filename xx temp;
data _null_;
file xx;
set names end=done;
length line $100;
if _n_ = 1
then line = cats('proc sql; select * from sashelp.class where name in ("',name,'"');
else line = cats(',"',name,'"');
put line;
if done then put ");quit;";
run;
%include xx /source2;
filename xx clear;
This will allow for a much longer list, as the size limit for SQL statements will be much higher than that for macro variables. Your code in the include should of course be tailored for explicit pass-through.
The easiest way to speed your query up is to upload the SSNBR values you are interested in to a temp Oracle table, and perform the inner join in Oracle.
Depending on the size of the lookup table, you could even try to make a WHERE condition out of it and put the data into the code.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.