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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.