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

Hello,

 

In the process of converting Access tables to SAS (9.4 TS Level 1m1 X64_SRV12), I read in a list of Access databases, followed by a list of all the tables in each database, and finally save each table in each database as a SAS file. This works great, except when the name of a table has consecutive spaces in it. Below is the piece of code giving me problems.

 

/*

DBNAMES is the filepath of the Access database

Name is the table name within the database

*/

 

proc sql;
     connect to access (path="&DBNAMES");
     create table output.&title as
     select * from connection to access
          (
               select * from `%superq(Name)`
          );
     disconnect from access;
quit;

The following error is then given:

ERROR: Describe: The Microsoft Access database engine cannot find the input table or query '02 - agent and emergency contact'. Make sure it exists and that its name is spelled correctly.

 

In reality there is an extra space between "-" and "agent" that is sent to the Access database, but for some reason is gets removed in the process. The same error is given even if the actual name of the table ('02 -  agent and emergency contact') is entered in place of `%superq(Name)`. I deduce that the space is removed, as even the method of using the actual table name with the consective spaces returns the error with only a single space in the table name that could not be found. Lastly, the reason this SQL Pass-Through is used, is beacuse it allows tables names of greater than 32 characters, which many of the tables in question contain.

 

Is there any way around this without going into the Access table and manually removing the extra blanks?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

As I use MS SQL server managent, my query looks like

 

SELECT TOP 100 [TABLE_SCHEMA]
,[TABLE_NAME]
FROM [MYDATABASE].[INFORMATION_SCHEMA].[TABLES]


You can use same query in pass through

proc sql;
     connect to access (path="&DBNAMES");
     create table output.&title as
     select * from connection to access
          (
               SELECT TOP 100 [TABLE_SCHEMA] ,[TABLE_NAME]
                 FROM [MYDATABASE].[INFORMATION_SCHEMA].[TABLES]
          );
     disconnect from access;
quit;

 

Similarly, you write the query MS Access editor, test it and then use the same query in pass through. It should work for you.

View solution in original post

8 REPLIES 8
RahulG
Barite | Level 11
proc sql;
     connect to access (path="&DBNAMES");
     create table output.&title as
     select * from connection to access
          (
               select * from    
                  '02 -  agent and emergency contact'n
          );
     disconnect from access;
quit;

Try this out put your table name with  ' 'n using SAS name literal

 

A SAS name literal is a name token that is expressed as a string within quotation marks, followed by the upper- or lowercase letter n

 

Refer to last section of this page - SAS Name Literals

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000998953.htm

LinusH
Tourmaline | Level 20
@RahulG i don't think that will work - have you tested it?
The n literal is SAS specific syntax, and everything that goes inside a SQL pass through block habe no notion abouthat SAS, just the target RDBMS (MS Access in this case).
Data never sleeps
RahulG
Barite | Level 11

@LinusH yes you are correct. I tried, SAS literal wont work here.

 

@jerry4 Write a query in MS Access that help to select rows from the table. Put the same query in pass through. I tried on MS SQL, it worked for me. 

jerry4
Calcite | Level 5
While I know SAS pretty well, my knowledge of Access and SQL is fairly limited. I'm not exactly sure what you mean by "Write a query in MS Access that help to select rows from the table. Put the same query in pass through".

Would it be possible to elaborate a bit?

Thanks so much for your help.
RahulG
Barite | Level 11

As I use MS SQL server managent, my query looks like

 

SELECT TOP 100 [TABLE_SCHEMA]
,[TABLE_NAME]
FROM [MYDATABASE].[INFORMATION_SCHEMA].[TABLES]


You can use same query in pass through

proc sql;
     connect to access (path="&DBNAMES");
     create table output.&title as
     select * from connection to access
          (
               SELECT TOP 100 [TABLE_SCHEMA] ,[TABLE_NAME]
                 FROM [MYDATABASE].[INFORMATION_SCHEMA].[TABLES]
          );
     disconnect from access;
quit;

 

Similarly, you write the query MS Access editor, test it and then use the same query in pass through. It should work for you.

jerry4
Calcite | Level 5
I don't know enough about SQL to try exactly what you did, but your post definitely made me realize I should be thinking about the problem in terms of SQL and not SAS.

By changing `%superq(Name)` to [%superq(Name)] the problem is solved.

Thanks for your help.
Ksharp
Super User
Can you use LIBNAME statement + PROC COPY to get it ?


jerry4
Calcite | Level 5
My understanding of proc copy is that it is used for copying SAS datasets into other types of files. Do you have an example as to how one would copy Access to SAS using proc copy, given that the table names in Access are longer than 32 characters?

Thanks!

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
  • 8 replies
  • 3408 views
  • 0 likes
  • 4 in conversation