How to import multiple access dataset into SAS in one step

Question

I have a huge mdb file containing multiple datasets that I would like to import into SAS library. But I have two problems: 1. Many datasets have a space in their name. 2. The name of the dataset is in Spanish/French

Is there a way that SAS may substitute the space with underscore "_" and import the dataset to a new library in one step without a macro? If not, macro is fine but I still need to take care of the space in the name of the dataset.

Thanks a lot!!

Joe Joe · Accepted Answer · 2014-10-27T19:14:16

Assuming the length of the member name is <= 32 characters, you should be able to do this (here accdb is whatever your libname to the access table is).

data want;
  set accdb.'Your Table Name'n;
run;

Also make sure to have OPTIONS VALIDMEMNAME=EXTEND; set if it's not already.

Spanish/French shouldn't be a problem, particularly if you're running in a Unicode environment, but to be sure what you can do is:

Define the libname
Query sashelp.vtable or dictionary.tables (the latter is in PROC SQL, the former is also in the data step).

Something like

data accdb_tables;
  set sashelp.vtable;
  where libname='ACCDB'; *whatever your access database libname is, all uppercase usually;
run;

That lets you see what SAS considers the names to be, so if it does translate the NLS characters you can see how it does so. If that doesn't work then I'd submit a new question with the specifics (what the exact table name is, what error you get when you try to use it). As long as it's in quotes with the n after it, though, it should generally work.

As far as importing multiple tables, if you use libname access you can make all of the tables available to SAS as if they were SAS datasets (I assume that you've done so in the above answers).

libname accdb 'c:\pathtodata\myfile.mdb';

You can also use OLEDB or ODBC if you have the proper drivers installed to do so.

Then I would just use them straight from the libname, the first time you need them; you likely don't need to copy them to SAS datasets prior to using. If you wanted to, you'd have to do a macro loop over the libname.

How to import multiple access dataset into SAS in one step

1 Answers