- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A generic solution would be just to use SQL:
select count(*) from MS_ACCESS_Table
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ....
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Does the Access table appear in dictionary.tables?
Proc sql;
select nobs
from dictionary.tables where libname='LIBRARY' and memname='TABLENAME';
quit;
might work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A generic solution would be just to use SQL:
select count(*) from MS_ACCESS_Table
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.