topic Re: How to see record count in a table where row limit has been reached in SAS Enterprise Guide

How to see record count in a table where row limit has been reached

SAS_Ryan — Thu, 03 Aug 2017 14:46:36 GMT

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:

Proc sql;

select count(*)

from table;

quit;

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?

Re: How to see record count in a table where row limit has been reached

TomKari — Thu, 03 Aug 2017 15:06:28 GMT

First, important, question. Are you trying this on SAS datasets, or on database tables that SAS is accessing through SAS/Access?

Tom

Re: How to see record count in a table where row limit has been reached

SAS_Ryan — Thu, 03 Aug 2017 15:21:26 GMT

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.

Re: How to see record count in a table where row limit has been reached

TomKari — Thu, 03 Aug 2017 23:53:16 GMT

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.

Tom

Re: How to see record count in a table where row limit has been reached

CaseySmith — Fri, 04 Aug 2017 04:38:29 GMT

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):

proc sql;

select * from sashelp.vtable

where libname eq "SASHELP" and memname eq "CLASS";

quit;

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.

Casey

Re: How to see record count in a table where row limit has been reached

LinusH — Fri, 04 Aug 2017 14:12:59 GMT

Yes, SPD Server doesn't use sas7bdat files. But that doesn't really matter, it's just the physical file format.
I don't have SAS at my fingertips to try, but I'm pretty sure that the record/observation count is available from SAS, like in PROC CONTENTS. Not 100% sure about SASHELP views though.
If you are particularly interested in SPD Server data structures, it offers SPD Server specifc DICTIONARY tables available through explicit SQL pass through:
http://support.sas.com/documentation/cdl/en/spdsug/68963/HTML/default/viewer.htm#p0zjxy7y9lagkxn1ad49k8xcsrvm.htm

Re: How to see record count in a table where row limit has been reached

Ksharp — Sat, 05 Aug 2017 12:27:02 GMT

Why not use distionary table ?

proc sql;
create table n_obs as
 select *
  from dictionary.tables
   where libname='SASHELP' and memtype='DATA';
quit;