08-03-2017 10:46 AM
I'm looking to see the number of records in a table, and am frustrated by EG's behavior when a table exceeds row limits. Under table Properties -> Advanced I see 10,000 rows as the record count. This is in fact the row limit, not the number of records in the table, because if I run:
I get the actual number of rows. Of course I can run proc sql for every table, but thats a pain. In Base SAS table properties would give you the actual row count. Am I missing something in EG? Why is this so inconvenient?
08-03-2017 11:06 AM
First, important, question. Are you trying this on SAS datasets, or on database tables that SAS is accessing through SAS/Access?
08-03-2017 11:21 AM
Data is on a SAS SPD server. Which being a SAS product, I thought stored data as sas7bdat files. I guess I thought wrong!! Removing the row limit for ACCESS datasets does fix the problem.
08-03-2017 07:53 PM
I'm sorry to say, my experience with SPD is limited.
With SAS datasets, I believe the row count is stored in the table descriptor, and is no problem to access.
The problem with database tables is that SAS has no visibility into the row count, so it has to do a "SELECT COUNT(*)", which particularly with complex views, can be very expensive in terms of database resources. So, personally, I agree with SAS not doing it automatically.
SPD is an interesting question. I would have thought that the record count would have been easily available to SAS via the table descriptor, but I'm not that knowledgeable. Hopefully someone else will chime in.
08-04-2017 12:38 AM
Is the record count accurate or missing value in the sashelp.vtable dictionary table for your SPD data?
For example, submit the following (replacing with your SPD libname and a memname in that library):
select * from sashelp.vtable
where libname eq "SASHELP" and memname eq "CLASS";
Does the "Number of Physical Observations" (nobs) and "Number of Logical Observations" (nlobs) correctly report the record count for your data? Or is it missing value? (I'm curious whether the record count is surfaced through the SPD engine.)
If the dictionary table does return the record count for your data, the issue may be in EG for not exposing it.
As Tom indicated, SAS can't cheaply determine the record count for DBMS (it would have to make a pass through the data, which is expensive, thus avoids). The record count in the sashelp.vtable dictionary is a missing value in this case. Same goes for SAS views, since the data for views is dynamically retrieved.
08-04-2017 10:12 AM
08-05-2017 08:27 AM
Why not use distionary table ?
proc sql; create table n_obs as select * from dictionary.tables where libname='SASHELP' and memtype='DATA'; quit;