topic Re: Load last n obs from oracle in SAS Programming

Load last n obs from oracle

acordes — Thu, 19 Jan 2023 11:54:49 GMT

The attached code works, but it takes as long time as reading all the oracle rows.

I want him to START reading at row 10000000 like the firstobs option tells him.

proc sql;
   connect to oracle as dbcon
       (user=SAS password='xxxxxxxx' buffsize=100
        PATH=xxxxxx.vwfsag.de);
select * into :noby
   from connection to dbcon
     ( select 
          count(*)  from fact_ope_form);
quit;

%put &noby.;
%let numobs=100;

data _null_;
f1=&noby. - &numobs;
call symputx("first_o", f1);
call symputx("last_o", &noby.);
run;

%put &first_o. &last_o.;

data MKT.aki(promote=no);
set myora.fact_ope_form(obs=&last_o. firstobs=&first_o.);
keep fec: _fec: dummy;
_fecformo=datepart(fecformo);
dummy=1;
run;

Re: Load last n obs from oracle

ballardw — Thu, 19 Jan 2023 16:03:37 GMT

Is your set

 myora.fact_ope_form

actually an Oracle table connected to SAS?

Have you looked to see if Oracle supports such an action? Doing it with pass through and native Oracle tools would likely run much quicker. As a minimum I would look to using Oracle dictionary tables, or equivalent, to query the data base for details about the number of records in the set instead of using the Count(*) which would load the entire set to get a piece of information likely stored elsewhere in the Oracle system.

NOT an Oracle guru, so don't know the exact place to look.

Re: Load last n obs from oracle

Reeza — Thu, 19 Jan 2023 16:11:33 GMT

SQL databases don't reinforce row order, so reading by location can be tricky. Is there another way of specifying this condition instead?
https://asktom.oracle.com/pls/apex/asktom.search?tag=operations-preserving-row-order

all_table is the dictionary table in Oracle and num_rows has the number of rows.

https://dataedo.com/blog/useful-oracle-data-dictionary-queries-every-dba-should-have

Re: Load last n obs from oracle

acordes — Thu, 19 Jan 2023 16:10:44 GMT

Thanks, the "solution" I've found is to sort the oracle table in the data step, in this case the start date of the contracts.

The loading time for these tables is quite ok, I do not complain for the medium size tables up 2 million rows.

Having 20 million rows slows down a lot and normally I only need the last year's data.

This approach improves my situation at least.

data MKT.aki(promote=no);
set myora.fact_ope_form(obs=10000);
by descending fecformo;
run;

Re: Load last n obs from oracle

ballardw — Thu, 19 Jan 2023 19:03:49 GMT

@acordes wrote:

Having 20 million rows slows down a lot and normally I only need the last year's data.

If there is anything resembling a DATE valued variable in the data then a WHERE clause in data selection should get you "last year's data". Implement that correctly and it should reduce times a lot.