SQL Pass-Through to Access - Table Names with Consecutive Spaces

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SQL Pass-Through to Access - Table Names with Consecutive Spaces

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!


Accepted Solutions
Solution
‎08-05-2016 10:48 AM
Super Contributor
Posts: 259

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

[ Edited ]

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


All Replies
Super Contributor
Posts: 259

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

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

Super User
Posts: 5,386

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

@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
Super Contributor
Posts: 259

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

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

New Contributor
Posts: 4

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

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.
Solution
‎08-05-2016 10:48 AM
Super Contributor
Posts: 259

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

[ Edited ]

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.

New Contributor
Posts: 4

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

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.
Super User
Posts: 9,867

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

Can you use LIBNAME statement + PROC COPY to get it ?


New Contributor
Posts: 4

Re: SQL Pass-Through to Access - Table Names with Consecutive Spaces

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!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 709 views
  • 0 likes
  • 4 in conversation