Char length being truncated

[HN2001]

I have a column in excel that exceeds 255.  When I read the data in and use dbsastype to force the column char and length, it still gives me 255.  Any ideas of what I'm doing wrong.

Here is some of the code:

LIBNAME WrkBk EXCEL "file" MIXED=YES;

DATA test.;
SET WrkBk."sh1"n(
DBSASTYPE=("f1"='char(450)'));
RUN;
LIBNAME WrkBk CLEAR;

[art297]

Have you tried it with dbms=xlsx. Unfortunately, it doesn't allow mixed and dbsastype, but often does what one wants. e.g.:

LIBNAME WrkBk xlsx "/folders/myfolders/book1.xlsx";

DATA test;
  SET WrkBk.sheet1;
RUN;

LIBNAME WrkBk CLEAR;

Art, CEO, AnalystFinder.com

[HN2001]

I did try that and it didn't work. I needed to be able to set the length because I was reading in multiple files with a macro around that libname statement and of course the lengths in each file were different. I think there are limitations with xlsx and the length of the variable. I ended up reading in the data with a proc import and setting the length in a data step. Thanks for your suggestion

[Kurt_Bremser]

Take control. Save the data to a csv file and read that with a data step. The Excel file format is unusable as a reliable transport.

[HN2001]

I agree I am definitely seeing limitations with libname excel and libname xlsx, even using proc import I see variations in how the data is read in with xlsx and excel

[Ksharp]

Try another option.

LIBNAME WrkBk EXCEL "file" MIXED=YES dbmax_text=32767 ;

DATA test;
SET WrkBk."sh1"n;
run;

[HN2001]

Unfortunately that didn't work either

[HN2001 · Accepted solution]

I ended up resorting back to a proc import statement and then setting the length in a data step that followed the proc import. From googling, I think there are limitations with the libname excel and libname xlsx.