topic Re: Is it possible to count the number of rows in a dataset in CAS without loading the table? in SAS Viya

Is it possible to count the number of rows in a dataset in CAS without loading the table?

shiney_martin1 — Tue, 11 Jul 2023 09:21:53 GMT

I am writing a program which will detect all the tables, and the columns in each of these tables, in multiple caslibs my program has access to.

This is effectively a data crawler, detecting what data we have access to.
However, there is a problem that some of these tables are empty, so I don't want to have them listed in the final output, as these tables are not useful.

So the solution is that as I go through the tables, I would like to check the number of rows, however I can only find solutions that load the table, then checks the number of rows. This is problematic as the program has access to thousands of tables, and some of them are hundreds of millions of rows, so take 20+ minutes to load. So the overall data crawler would take over a day to run.

Is there a way to count the number of rows of a CAS table without loading it into memory?

This is the current code I use to check the number of rows in the table.

	proc cas;
  	    simple.numRows result=rows  table={caslib="&caslib_name.",name="&table_name." } ; 
	    call symputx("nrows",rows.numrows,"L");
	quit;

Thanks for your help.

Re: Is it possible to count the number of rows in a dataset in CAS without loading the table?

sbxkoenk — Tue, 11 Jul 2023 11:26:52 GMT

Good question! (I don't have the answer 😞).

For clarity,

you have SAS-tables saved as *.SASHDAT (in-memory format) files in several path-based caslibs.

Now you want to check the n° of records (n° of observations) in the *.sashdat data sets without uploading to memory first ... Correct?

Not sure whether it's possible, but I guess other people will chime in to explain more.

Koen

Re: Is it possible to count the number of rows in a dataset in CAS without loading the table?

shiney_martin1 — Tue, 11 Jul 2023 12:29:47 GMT

Thanks for helping me clarify.

Most of the caslibs are actually connections to Oracle databases.

Which makes me think that I should potentially explore using directly querying the Oracle database for any information it holds on the tables, rather than accessing it via the caslib connector?
As presumably, SAS will struggle to operate on the tables without loading them first, as they're not currently SAS datasets.