How do I import MS Access files with multiple tables that have space in the name into SAS-CodePudding

I import several files into SAS daily and each file has multiple tables. These tables have long names >32 and include spaces and slashes: I use the following two codes for the import.

The first code import only one table from one file, it works around getting the tables with unsupported names such as below:

proc sql;
  connect to access (path='C:\Users\c227466\Desktop\Testing access to excel\Acess Downloads\Inc_Cauris.accdb');  
  create table work.testing as select * from connection to access
    (
      select * from [Laboratory Information (system)]
    );
  disconnect from access;
quit;

The second one import all tables in the file but would error out those tables with unsupported names as below:

libname out access path="C:\Users\c227466\Desktop\Testing access to excel\Acess Downloads\Inc_Cauris.accdb";

proc copy in=out out=work;
run;

My question: Is there a way/code to work on both issues and get all tables no matter what the names are? Another question: Is there a way to get all the files instead of one file at a time?

Thank you

CodePudding user response：

Did you try

options validmemname=extend;

Note that will not help for "tables" with names that are too long (more than 32 bytes) to be valid dataset names in SAS.

CodePudding user response：

You have to use both methodologies.

My suggested approach would be to use the library method with the option @Tom mentioned for all data sets and then use the SQL Pass through (SQL method) for the few tables that have names longer than 32 characters.

Another possibility is to create a view of the tables with the long names in Access that have shorter names and then you can use the libname method alone. But if you don't have control over the Access DB you will have to use both methods.