topic Re: WARNING: Failed to scan text length with Excel libname in SAS Programming

WARNING: Failed to scan text length with Excel libname

evp000 — Thu, 17 Nov 2016 15:50:47 GMT

Hi all,

I keep getting this warning

WARNING: Failed to scan text length or time type for column xxx

I'm using the Excel libname and reading the file with the data step. I have already checked the registry and TypeGuessRows=0.

I read the same file repeatedly and get this message for the same column every time. I tried forcing a length:

set ln."Tabname$"n (dbsastype = (xxx= 'char(500)'));

but that doesn't work.

There's no problem with the data. I just don't want to see this message in the log.

V9.2.

Anyone have any suggestions?

Thanks.

Re: WARNING: Failed to scan text length with Excel libname

RW9 — Thu, 17 Nov 2016 15:59:46 GMT

Sounds like you have some garbage in your Excel file. Not surprising, Excel really shouldn't be used for any data purpose. A Google found this discussion, you could try some of the options therein:

https://groups.google.com/forum/#!topic/comp.soft-sys.sas/Tcg6V7Sj8vk

Not much we can really do, are not able to recreat your issue, and I wouldn't download Excel files myself anyways.

Re: WARNING: Failed to scan text length with Excel libname

evp000 — Thu, 17 Nov 2016 18:12:30 GMT

Thanks, that was helpful. If I set scan_text to 'no' then specify the lengths myself, it works but I don't want to set the lengths myself for the rest of the sheets. It means I have to have one libname with scan_text=no, used for this one sheet, and another with scan_text=yes for the rest of the sheets, even though it's the same excel file.

So with scan_text=no I could

a) specify the lengths with a length statement, in which case I had to set option VARLENCHK = NOWARN for this step since the lengths were conflicting with the default lengths SAS wanted to assign. And get rid of the informats.

b) set the lengths with (DBSASTYPE=...). It took me a while since, when specifying the source column from Excel, you have to use "Column Name"n <- n for the original name with spaces.

Re: WARNING: Failed to scan text length with Excel libname

ballardw — Thu, 17 Nov 2016 18:15:04 GMT

Do want to correct the data to suppress the message or just not show the message?

Re: WARNING: Failed to scan text length with Excel libname

evp000 — Thu, 17 Nov 2016 18:25:32 GMT

The data is ok, in fact, I don't even use that column. I just don't want the message. Could I delete that one variable at source, in the set statement? How? With the Excel column name?

set specns."Sheetname$"n ;