DATA Step, Macro, Functions and more

WARNING: Failed to scan text length with Excel libname

Reply
Contributor
Posts: 74

WARNING: Failed to scan text length with Excel libname

[ Edited ]

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.

Super User
Super User
Posts: 7,407

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

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.

Contributor
Posts: 74

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

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.

 

Super User
Posts: 10,516

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

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

Contributor
Posts: 74

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

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  ;

Ask a Question
Discussion stats
  • 4 replies
  • 305 views
  • 0 likes
  • 3 in conversation