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;
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
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.
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;
@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.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.