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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: