topic Re: How to use "obs=" and "firstobs=" in cas actions? in SAS Programming

How to use "obs=" and "firstobs=" in cas actions?

acordes — Thu, 19 Jan 2023 10:49:56 GMT

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;

Re: How to use "obs=" and "firstobs=" in cas actions?

SASJedi — Thu, 19 Jan 2023 21:23:11 GMT

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

Re: How to use "obs=" and "firstobs=" in cas actions?

acordes — Thu, 19 Jan 2023 21:45:40 GMT

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...