BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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
1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

A generic solution would be just to use SQL:

  select count(*) from MS_ACCESS_Table

View solution in original post

6 REPLIES 6
jakarman
Barite | Level 11

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 --<-----
ballardw
Super User

Does the Access table appear in dictionary.tables?

Proc sql;

     select nobs

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

quit;

might work.

PaigeMiller
Diamond | Level 26

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
SASKiwi
PROC Star

A generic solution would be just to use SQL:

  select count(*) from MS_ACCESS_Table

PaigeMiller
Diamond | Level 26

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
jakarman
Barite | Level 11

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 --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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