topic Importing Long Text from Excel in SAS Programming

Importing Long Text from Excel

rajd1 — Fri, 10 Jul 2020 06:05:16 GMT

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

Re: Importing Long Text from Excel

hyo_tree — Fri, 10 Jul 2020 06:51:20 GMT

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.

Re: Importing Long Text from Excel

Patrick — Fri, 10 Jul 2020 07:49:28 GMT

@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;

Re: Importing Long Text from Excel

hyo_tree — Fri, 10 Jul 2020 09:50:06 GMT

thank you . I got it.

Re: Importing Long Text from Excel

smantha — Fri, 10 Jul 2020 10:08:36 GMT

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.

Re: Importing Long Text from Excel

Ksharp — Fri, 10 Jul 2020 12:27:32 GMT

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

Re: Importing Long Text from Excel

newsas007 — Fri, 10 Jul 2020 17:30:41 GMT

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;

Re: Importing Long Text from Excel

newsas007 — Fri, 10 Jul 2020 17:30:48 GMT

The Scantext option did not work.