BookmarkSubscribeRSS Feed
SAS_Ryan
Fluorite | Level 6

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?

6 REPLIES 6
TomKari
Onyx | Level 15

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

 

Tom

SAS_Ryan
Fluorite | Level 6

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.

TomKari
Onyx | Level 15

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

CaseySmith
SAS Employee

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


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

LinusH
Tourmaline | Level 20
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#p0zjxy7y9lagkxn1ad4...
Data never sleeps
Ksharp
Super User

Why not use distionary table ?

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2672 views
  • 0 likes
  • 5 in conversation