I'm looking for a sibling of the obs= option for a cas action.
LIBNAME myora ORACLE PATH=xxxxxx.vwfsag.de USER=SAS PASSWORD='xxxxxxx';
data MKT.aki(promote=no);
set myora.FACT_OPE_FORM(obs=10000);
run;
How can I read only x rows for a cas action?
It takes really long because it performs a serial load...
Or I read only few rows or I convert this to parallel load.
NOTE: Active Session now mysession. NOTE: Performing serial LoadTable action using SAS Data Connector to Oracle. 95 quit; NOTE: The PROCEDURE CAS printed page 406. NOTE: PROCEDURE CAS used (Total process time): real time 1:22.75 cpu time 0.18 seconds 96 97 %studio_hide_wrapper; 108 109
proc cas;
session mysession;
simple.topk /
aggregator='N', topk=10, bottomk=0,
inputs={"INDISMAN"},
casout={name='topk', caslib="public", replace=true},
table={
name="FACT_OPE_FORM", CASLIB="oracaslib",
where="1=1",
computedVars={{name="year"}},
computedVarsProgram="year=year(datepart(FECFORMO))",
groupBy={'year'}};
run;
quit;
To avoid haiving to load the entire DBMS table into CAS memory for processing, you need to first subset the rows in the database. And in CAS, only FedSQL can perform the implict or explicit pass-through to a database required to do that. So, reproduce the effect of OBS= in the DATA step, you can use FedSQL with the LIMIT clause:
proc fedsql sessref=mySession;
create table MKT.aki as
select *
from myora.FACT_OPE_FORM
limit 1000
;
quit;
The DBMS SQL generated to retrieve the data will include the appropriate syntax to limit the number of rows retrieved. Because databases don't store their tables in a sequential format, the concept of FIRTSTOBS has no equivalent in DBMS SQL, so I know of no way to mimic that effect in the database.
As for CAS actions themselves, the action may or may not provide options to limit the rows input or output (like the TO option for table.fetch), but because CAS actions only process CAS tables, the DBMS table data will have to have been pre-loaded into memory before running the action. So, the best answer is to use FedSQL to pre-load an abbreviated table, and then process that with the CAS action.
I hope this helps.
Mark
To avoid haiving to load the entire DBMS table into CAS memory for processing, you need to first subset the rows in the database. And in CAS, only FedSQL can perform the implict or explicit pass-through to a database required to do that. So, reproduce the effect of OBS= in the DATA step, you can use FedSQL with the LIMIT clause:
proc fedsql sessref=mySession;
create table MKT.aki as
select *
from myora.FACT_OPE_FORM
limit 1000
;
quit;
The DBMS SQL generated to retrieve the data will include the appropriate syntax to limit the number of rows retrieved. Because databases don't store their tables in a sequential format, the concept of FIRTSTOBS has no equivalent in DBMS SQL, so I know of no way to mimic that effect in the database.
As for CAS actions themselves, the action may or may not provide options to limit the rows input or output (like the TO option for table.fetch), but because CAS actions only process CAS tables, the DBMS table data will have to have been pre-loaded into memory before running the action. So, the best answer is to use FedSQL to pre-load an abbreviated table, and then process that with the CAS action.
I hope this helps.
Mark
Thank you @SASJedi for this explanation.
It works but it is slower than the data step load with an obs=1000 statement and a by descending sort.
By far the fastest solution I've found is this:
data mkt.aki;
set myora.FACT_OPE_FORM(OBS=10000);
BY DESCENDING FECFORMO;
_fecformo=datepart(fecformo);
run;
proc summary data=mkt.aki print n;
class _fecformo;
format _fecformo year.;
var fk_obj;
run;
What catches my eye with your fedsql code is the following:
the limit option seems to have an effect like the obs option in a data step.
The 1000 observations written to the castable belong to the oldest records...
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 25. 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.