BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
L5ive
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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.

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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?

L5ive
Obsidian | Level 7
SSNBR_LIST is about 10 M Row and its only one column.
i tried the option offered by @ChrisNZ and it worked as a work around.
however i cant do this in all the codes or programs that im running since the access to write tables on the Oracle is limited.
i kind of understand your explanation however if you could guide me what should i try first or look into that would be great.

Thank you

Kurt_Bremser
Super User

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.

ChrisNZ
Tourmaline | Level 20

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.

L5ive
Obsidian | Level 7
I tried this and it worked with only 26 Min real time run (50% Less Time)

Thank you So much

SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2404 views
  • 4 likes
  • 3 in conversation