SAS Programming

DATA Step, Macro, Functions and more
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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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