Importing Long Text from Excel

[rajd1]

I have a couple of variables that have long text data in excel. When I import it with import statement it stops at 175 characters. I want to be able to get full text length.

PROC IMPORT OUT= WORK.ge DATAFILE= "filepath/name"

DBMS=xlsx REPLACE;

SHEET="One";

GETNAMES=YES;

RUN;

Thank you

[hyo_tree]

Hi rajd1,

please give the length of the variable you want to store the long text. Because SAS will default that the length of this variable is the length of the first data value you entered.

[Patrick]

---

@hyo_tree wrote:
Hi rajd1,

please give the length of the variable you want to store the long text. Because SAS will default that the length of this variable is the length of the first data value you entered.

---

@hyo_tree This statement is not correct. Proc Import with the XLSX engine will use the longest string to assign the variable length.

@rajd1 

Below code reads the attached Excel where the longest string for column VarA is in cell A5000 - and it gets it right.

What happens on your end if you use this code to read the attached Excel?

proc import 
  out= work.ge 
  datafile= "~/test/test.xlsx"
  dbms=xlsx 
  replace;
  sheet="one";
  getnames=yes;
run;

proc contents data=work.ge;
run;quit;

[hyo_tree]

thank you . I got it.

[Ksharp]

or Try:
libname x xlsx 'c:\...' dbmax_text=32767;

[newsas007]

Thank you this worked

libname db "C:\excel files\test1.xls" mixed=yes;
data test3;
set db."Sheet1$"n(DBSASTYPE=(var='char(1024)'));
run;
libname db;

[smantha]

Please try scantext option.
SCANTEXT applies to columns containing text. If SCANTEXT=YES, then SAS will scan the column for the longest string, and uses its length as the column width.

[newsas007]

The Scantext option did not work.