topic Re: importing access data with a long table name in SAS Procedures

importing access data with a long table name

robin2 — Fri, 24 Jan 2020 23:08:55 GMT

Hi,

I am trying to import data from a Microsoft Access data table that has a table name longer than 32 characters.

proc import out = work
datatable = "verylongtablenameherexxxx xxxxxxxxx"
dbms = access replace;
dbpwd = "xxxx";
database = "pathway to mdb";
run;

I get the following error message: ERROR: TABLE value exceeds maximum length of 32 characters.

Is there a way to get around this? I tried using the SQL pass through (which I have never used before) but that also presents an error.

proc sql;
connect to access(path="pathway to .mdb"
	dbpwd = "xxxx");
create table work.test as
select * from connection to access
(select * from "verylongtablenameherexxxx xxxxxxxxx");
disconnect from access;
quit;

ERROR: Prepare: Syntax error in query. Incomplete query clause.
SQL statement: select * from "Live Booster Phone calls Information"

Any ideas would be greatly appreciated.

Thank you,

Robin

Re: importing access data with a long table name

Reeza — Fri, 24 Jan 2020 23:13:41 GMT

Pass Through should work. I think your query is wrong though you need to use Access SQL and indicate the book and table name? I think it needs to be in braces or there's some way that Access references tables with spaces in them.

This is the part SAS doesn't like right now:

select * from "verylongtablenameherexxxx xxxxxxxxx"

@robin2 wrote:

Hi,

I am trying to import data from a Microsoft Access data table that has a table name longer than 32 characters.
proc import out = work
datatable = "verylongtablenameherexxxx xxxxxxxxx"
dbms = access replace;
dbpwd = "xxxx";
database = "pathway to mdb";
run;
I get the following error message: ERROR: TABLE value exceeds maximum length of 32 characters.

Is there a way to get around this? I tried using the SQL pass through (which I have never used before) but that also presents an error.
proc sql;
connect to access(path="pathway to .mdb"
	dbpwd = "xxxx");
create table work.test as
select * from connection to access
(select * from "verylongtablenameherexxxx xxxxxxxxx");
disconnect from access;
quit;
ERROR: Prepare: Syntax error in query. Incomplete query clause.
SQL statement: select * from "Live Booster Phone calls Information"

Any ideas would be greatly appreciated.

Thank you,

Robin

Re: importing access data with a long table name

robin2 — Fri, 24 Jan 2020 23:42:50 GMT

Hi Reeza,

Thanks for the speedy reply. You were right - I needed to put the table name in square brackets. Now the pass through works!

Thanks so much,

Robin

proc sql;
connect to access(path="pathway to .mdb"
	dbpwd = "xxxx");
create table work.test as
select * from connection to access
(select * from [verylongtablenameherexxxx xxxxxxxxx]);
disconnect from access;
quit;