I want to read an Excel spreadsheet without truncating values where the first rows contain only short values. I know the guessingrows statement for delimited files but that's not applicable to xls. To create a tab delimited file is not an option as the spreadsheet contains cells with CR characters ('0D'x) which create addtional lines when exporting from Excel.
Are you talking about numbers or strings?
strings
Some options:
1) File->SaveAs->CSV. Datastep import with delimiter=",". where col1 ne "";
2) Add XXXXXXX to the length required in your spreadsheet as first row.
3) Check out libname xls, which may or may not work.
4) There is also a system option call something like guessrows in the systems registry which should be updated to a large number.
Sorry but neither csv, tab or other delimiter will work unless you find an option to not interprete CRs in single cells as carriage return and thus new line. And as I said I know that guessingrows works with csv, tab etc but not xls (I assume for the entry in the registry holds the same)
Perhaps you could post the Excel file to show these carriage returns? Do you mean you have a special symbol for carriage returns in your data or just the alt/enter within cell?
The guessing rows is applicable to proc import for Excel files, please see the guidance at: Base SAS(R) 9.3 Procedures Guide, Second Edition
I think the registry change affects both, the actual option on the import may not.
Just to add, have you tried using the libname:
libname mybook excel "....xls" mixed=yes getnames=yes;
Interaction: | The GUESSINGROWS statement is valid only for delimited files. |
Did you see this in your reference?
Unfortunately I'm using SAS 9.1.3 remote for a client, so I can't easily check all possibilities. But I see libname is worth a trial
As to CR, this is inserted by Excel when you enter a line wrap in a cell (Alt+Enter). You can see this when you copy the cell content into an Editor with optional hex display (like UltraEdit). And exporting from Excel to a delimited file, then additionally adds a CRLF ('0D0A'x) after the '0D'x
Hi,
Yes, I posted the wrong web page. Please see this one: SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition
Specifically the section below: MIXED=YES|NO
You can also find/replace in Excel:
Open the find/replace dialog, in the find box do Alt 010 - that zero one zero. In replace put a space. I have seen it stated that Cntrl + j instead of the Alt works. You could also write a small VBA and replace CHAR(10) with a space on all cells.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.