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.
A generic solution would be just to use SQL:
select count(*) from MS_ACCESS_Table
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 ....
Does the Access table appear in dictionary.tables?
Proc sql;
select nobs
from dictionary.tables where libname='LIBRARY' and memname='TABLENAME';
quit;
might work.
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.
A generic solution would be just to use SQL:
select count(*) from 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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.