topic Re: String data truncated to 3 characters when import from Access in SAS Procedures

String data truncated to 3 characters when import from Access

ducdien — Fri, 07 Nov 2014 17:10:51 GMT

I have an Access database (accdb) with several long text variables. When I import it to SAS, they are truncated to 3 characters (including many and with a question mark, like xx?)

Please advise!

Thank you

Re: String data truncated to 3 characters when import from Access

ballardw — Fri, 07 Nov 2014 17:16:56 GMT

It will help to show the code used.

Re: String data truncated to 3 characters when import from Access

ducdien — Fri, 07 Nov 2014 18:22:38 GMT

This is what I use

Proc import out=test datatable="datatable" dbms=access replace;

database="path";

scanmemo=yes;

usedate=no;

scantime=yes;

run;

Thank you

Re: String data truncated to 3 characters when import from Access

ballardw — Fri, 07 Nov 2014 20:37:36 GMT

What do you get when you run proc contents on the dataset Test?

Re: String data truncated to 3 characters when import from Access

ducdien — Fri, 07 Nov 2014 20:52:04 GMT

When I run proc content, those variables show up as "Char, length 3, format $3, informat $3"

Re: String data truncated to 3 characters when import from Access

ballardw — Fri, 07 Nov 2014 21:02:07 GMT

I would suspect the MEMOSIZE option was set to 3 but you didn't show that in the code.

If the source data set isn't too large I would try exporting if from Access to a CSV file and look at that. If that looks okay then import that into SAS and see if there are still issues. If so, then you may issues related to the actual content.

Re: String data truncated to 3 characters when import from Access

ducdien — Fri, 07 Nov 2014 21:12:42 GMT

I tried to set memosize to a big number but it did not work

When I exported it from Access to Excel, things looked OK (but I lost a date/time variable which only the date part showed up) and I was able to import to SAS without that truncate problem.

But I hope we could do the import from Access without going through Excel

Re: String data truncated to 3 characters when import from Access

ducdien — Thu, 13 Nov 2014 13:52:20 GMT

Any suggestions on this?

Thank you

Re: String data truncated to 3 characters when import from Access

RW9 — Thu, 13 Nov 2014 14:24:05 GMT

I would suggest that you export your access data to CSV, then datastep to read it in - this gives the benefit of being able to see the data in plain text and having control over what happens at read in time. I would also recommend not using Access, but that's just my opinion.

Re: String data truncated to 3 characters when import from Access

Ksharp — Thu, 13 Nov 2014 15:04:37 GMT

Not sure , if it could help you . Check dataset option dbtype=(yourvarname char(200))

Re: String data truncated to 3 characters when import from Access

ebowen — Thu, 13 Nov 2014 17:23:18 GMT

By default, Proc Import uses the first 20 rows of the dataset to determine the length of the variables. If your memo field doesn't have any content for the first few records, SAS might assume that the field is short. You can try using the guessingrows option in proc import to increase the number of rows SAS uses to determine the variable size. Just add the following statement somewhere after the import statement (I used 100 rows as an example, but this can be anything):

guessingrows=100;

If you really want to control the size of the variable, though, you'll probably have to use a data step instead of Proc Import.