DATA Step, Macro, Functions and more

How to determine number of rows in a MS Access table

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,913
Accepted Solution

How to determine number of rows in a MS Access table

I want my SAS program to query a MS Access table and determine the number of rows in the table. How can I do this?

I have a macro that works well on SAS data sets, using the ATTRN function; but this returns a -1 for MS Access tables, and according to the docs for ATTRN, a -1 means that the number of rows is not available.

I suppose I could write a program that copies the entire table to a SAS data set, but if the MS Access table is huge, this is requires a lot of time to accomplish the task.


Accepted Solutions
Solution
‎11-14-2013 05:47 PM
Super User
Posts: 3,250

Re: How to determine number of rows in a MS Access table

Posted in reply to PaigeMiller

A generic solution would be just to use SQL:

  select count(*) from MS_ACCESS_Table

View solution in original post


All Replies
Trusted Advisor
Posts: 3,212

Re: How to determine number of rows in a MS Access table

Posted in reply to PaigeMiller

It should be possible as ms-access is having that type of information. http://msdn.microsoft.com/en-us/library/office/bb208624(v=office.12).aspx

Perhahps the special odbc queries SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition are working

perhaps ....

---->-- ja karman --<-----
Super User
Posts: 11,343

Re: How to determine number of rows in a MS Access table

Posted in reply to PaigeMiller

Does the Access table appear in dictionary.tables?

Proc sql;

     select nobs

     from dictionary.tables where libname='LIBRARY' and memname='TABLENAME';

quit;

might work.

Trusted Advisor
Posts: 1,913

Re: How to determine number of rows in a MS Access table

Yes, it appears in the dictionary tables, but the column in the dictionary table that contains the number of rows is missing for MS Acess tables.

Solution
‎11-14-2013 05:47 PM
Super User
Posts: 3,250

Re: How to determine number of rows in a MS Access table

Posted in reply to PaigeMiller

A generic solution would be just to use SQL:

  select count(*) from MS_ACCESS_Table

Trusted Advisor
Posts: 1,913

Re: How to determine number of rows in a MS Access table

Thanks, SASKiwi, that works.

I did not know you could use a structure such as count(*), I thought you had to specify a variable name.

Trusted Advisor
Posts: 3,212

Re: How to determine number of rows in a MS Access table

Posted in reply to PaigeMiller

I did not know you diid not know you could do SQL to MS-SQL-access. I was thinking you were asking could do SQL and behaving like a DBMS.    

SQL COUNT() Function (w3schools.com) it is showing a lot more functionality as just the count. A lot o them will work.  

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 3023 views
  • 0 likes
  • 4 in conversation