DATA Step, Macro, Functions and more

Char length being truncated

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Char length being truncated

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;


Accepted Solutions
Solution
‎03-23-2018 11:34 AM
Occasional Contributor
Posts: 12

Re: Char length being truncated

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.

View solution in original post


All Replies
Super User
Posts: 8,220

Re: Char length being truncated

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

 

 

Occasional Contributor
Posts: 12

Re: Char length being truncated

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
Super User
Posts: 10,599

Re: Char length being truncated

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

Re: Char length being truncated

Posted in reply to KurtBremser
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
Super User
Posts: 10,850

Re: Char length being truncated

Try another option.

 

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

DATA test;
SET WrkBk."sh1"n;
run;
Occasional Contributor
Posts: 12

Re: Char length being truncated

Unfortunately that didn't work either

Solution
‎03-23-2018 11:34 AM
Occasional Contributor
Posts: 12

Re: Char length being truncated

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 128 views
  • 3 likes
  • 4 in conversation