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!
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.
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
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.
Can you use LIBNAME statement + PROC COPY to get it ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.