Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

PROC SQL Pass through MS ACCESS

Status
Not open for further replies.

smalek

Programmer
Jan 15, 2009
28
CA
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
 
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

 
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.

Code:
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:

 
I suppose you provided enough info...

Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top