topic Re: Proc Import: A table with a long name in SAS Procedures

Proc Import: A table with a long name

Nupur20 — Thu, 08 Mar 2012 21:17:24 GMT

Hi,

I am trying to import tables into SAS from an access database and its giving me an error "Table values exceeds the limit of 32 characters".

This is the code I am using:

Proc import data = 'db0_xyz_edf_erfcbgt_hospital_clinic_rate_xxxx"

out = test

DBMS = Access

Replace;

database = "C:/.......mdb";

run;

Is thera a way of importing data with a long name. I would appreciate oyur time and help regrading the same.

thanks!

Proc Import: A table with a long name

art297 — Thu, 08 Mar 2012 22:48:10 GMT

Are you on 9.3? Both the 9.2 and 9.3 documentation state:

When importing Microsoft Access tables, SAS/ACCESS converts the table name to a SAS member name. SAS does not support member names longer than 32 bytes.

However, the 9.3 documentation, link below, indicates that the method will accept up to the MS allowed 64 characters, but will likely truncate the table name to the SAS-allowed 32 characters. It will be useful to know if that is really true in 9.3

If you don't have 9.3 and can post a copy of the file, I'd be glad to test it.

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#p0psac3j16cioen1nq2hkwrnk55y.htm

Proc Import: A table with a long name

Reeza — Thu, 08 Mar 2012 23:15:56 GMT

Assuming you're on windows with access you can do something like the following to see it.

I don't have SAS to test it at the moment though, so post back if it works.

libname test access 'path to access here';

Then you can navigate test for the table and import using a data step,

data want;

set test.'this is my really long name'n;

run;

Proc Import: A table with a long name

Nupur20 — Fri, 09 Mar 2012 15:45:56 GMT

@ Reeza: Its not working . Its giving me an error: "

The data set name cannot have more than 32 characters."Do you any other way of doing it?

Proc Import: A table with a long name

Nupur20 — Fri, 09 Mar 2012 15:47:30 GMT

@ Art297: Thanks for your reply and willingness to test my file. I dont have SAS 9.3. Is it just not possiblt to do in SAS 9.2? If I upload the file, how would I do it unitl I will not have 9.3?

Is there any other way?

Proc Import: A table with a long name

Reeza — Fri, 09 Mar 2012 18:35:31 GMT

Of course there is

SQL Pass Through Facility.

You are correct, if the table name is more than 32 chars it does not show up and you need to access it using SQL Pass Through.

proc sql;

connect to access(path="C:\Temp\testing.mdb");

create table work.test as

select * from connection to access

(select * from This_is_my_really_long_table_name_with_more_than);

disconnect from access;

quit;

Re: Proc Import: A table with a long name

cfoo — Wed, 16 Sep 2015 23:23:52 GMT

Hi @Reeza,

Any methods for dealing with long table names with spaces and/or special characters?

Thanks

Re: Proc Import: A table with a long name

Reeza — Thu, 17 Sep 2015 01:27:58 GMT

However you would deal with it in Access since the command is passed to Access.

Re: Proc Import: A table with a long name

anastatis — Wed, 03 Jul 2019 21:48:09 GMT

just tried it on 9.4 and no, it won' trunc the name. SAS would still give you the error msg same as in the OP.
https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acpcref&docsetTarget=p0jf3o1i67m044n1j0kz51ifhpvs.htm&locale=en

according to 9.4 documentation, "When importing Microsoft Access database tables,SAS/ACCESS converts the table name to a SAS member name. Note that SAS does not support member names longer than 32 bytes." So my understanding is it doesn't matter how many characters it supports for MS Access table name, because SAS has to convert it into a SAS member, restriction for SAS member name will apply, too.