topic Re: PCFF - Import tables with more than 32 characters from mdb in SAS Enterprise Guide

PCFF - Import tables with more than 32 characters from mdb

MariaD — Mon, 26 Nov 2018 14:16:43 GMT

Hi Folks,

I tried to import a table, using PCFF, which name is longer than 32 characters. When I use point-and-click I don't have any problem but using PCFF the following error appear:

26 proc import table='XXX_XX_XX_XX_XX_XX_XXXX_XXXX_XXXX'
27 dbms=accesscs
28 out=table replace;
29 database='\\folder\file.mdb';
30 server='IP';
31 port=9621;
32 serveruser='user';
33 serverpass=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
34 run;

ERROR: TABLE value exceeds maximum length of 32 characters.

There is any way to import it using SAS code?

Regards,

Re: PCFF - Import tables with more than 32 characters from mdb

SuryaKiran — Mon, 26 Nov 2018 14:33:34 GMT

Hi,

SAS cannot handle tables names that have longer than 32 characters, try creating views for those tables or you can run a pass-through query if possible.

Re: PCFF - Import tables with more than 32 characters from mdb

MariaD — Mon, 26 Nov 2018 16:04:58 GMT

Hi @SuryaKiran,

Thanks! But why using import from point and click works?

Regards,

Re: PCFF - Import tables with more than 32 characters from mdb

SASKiwi — Mon, 26 Nov 2018 19:13:32 GMT

Behind the scenes, the point-and-click interface copies the Excel file up to the remote SAS server as a delimited file with a shortened filename without using SAS code. That is why it works for longer filenames as it is not using SAS code to do the transfer.

BTW the 32-character limit is being increased in SAS 9.5 to 128 characters and is due out probably in the next 12 months.

Re: PCFF - Import tables with more than 32 characters from mdb

Vince_SAS — Sat, 01 Dec 2018 01:04:21 GMT

Try something like this SQL explicit pass-through code:

proc sql;
  connect to access (path='\\folder\file.mdb');  
  create table work.table as select * from connection to access
    (
      select * from XXX_XX_XX_XX_XX_XX_XXXX_XXXX_XXXX
    );
  disconnect from access;
quit;

Vince DelGobbo

SAS R&D