topic Re: Char length being truncated in SAS Programming

Char length being truncated

HN2001 — Thu, 22 Mar 2018 20:58:55 GMT

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;

Re: Char length being truncated

art297 — Thu, 22 Mar 2018 21:14:59 GMT

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

Re: Char length being truncated

Kurt_Bremser — Thu, 22 Mar 2018 21:20:58 GMT

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.

Re: Char length being truncated

Ksharp — Fri, 23 Mar 2018 13:38:17 GMT

Try another option.

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

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

Re: Char length being truncated

HN2001 — Fri, 23 Mar 2018 15:28:05 GMT

Unfortunately that didn't work either

Re: Char length being truncated

HN2001 — Fri, 23 Mar 2018 15:30:00 GMT

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.

Re: Char length being truncated

HN2001 — Fri, 23 Mar 2018 15:32:46 GMT

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

Re: Char length being truncated

HN2001 — Fri, 23 Mar 2018 15:33:49 GMT

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