WARNING: Failed to scan text length with Excel libname

[evp000]

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.

[RW9]

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.

[evp000]

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.

[ballardw]

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

[evp000]

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  ;