PROC SQL Pass through MS ACCESS

[smalek]

Hello
I have a number of tables residing in an MS ACCESS db which I would like to import into SAS without using the PROC IMPORT option.
My code is as follows:
Proc SQL;
Connect to access (path='S:\LAB STATS 09_10.mdb');
Create table ed.test as
select * from connection to access
(select * from E_Q Data CNTs);
disconnect from access;
quit;

NOTE: E_Q Data CNTs is the table of interest in MS ACCESS.

I keep getting the following error:

ERROR: Prepare: Syntax error in FROM clause.
SQL statement: select * from E_Q Data CNTs

Can someone please review my code? I do not understand the ERROR message.

Thanks

 

[dblan]

Just a quick guess, but it looks like your table "E_Q Data CNTS" in Access has spaces in the name.
SAS probably reads that as 3 different tables.
Maybe try "E_Q_Data_CNTS" as the table in the SAS code, or as a test rename the table in Access to something without spaces to see if you still get the error.

Dave

 

[mdieckman]

Try the validvarname any option. This allows spaces in table names. Also, try connecting to your access db as a libname.


Sorry just paraphrasing on the code, but hopefully this will move you in the right direction.

libname a "C:/access.mdb";

options validvarname=any;

proc sql...

from a.'dsname'n

to set it back:

options validvarname=v7;

See link here for more info:

http://www.colostate.edu/Services/ACNS/swmanuals/sasdoc/sashtml/lgref/z1031056.htm#z1031065

 

[mdieckman]

I suppose you provided enough info...

libname a "S:\LAB STATS 09_10.mdb";

options validvarname=any;

PROC SQL;
     CREATE TABLE A.DS   AS
     (select
     A.*

     FROM A.'E_Q Data CNTs'n      as A


     );
QUIT;

options validvarname=v7;

I don't have SAS in front of me though so I haven't tested the code.