08-21-2012 09:49 AM
I've received a spreadsheet that is formatted entirely as numeric data, but when I use PROC IMPORT, SAS is reading in some of these columns as character. I can't figure out why it's doing this at all. There are a fair amount of empty cells in this spreadsheet, but I have two columns that have the exact same rows that are missing, and one column has been read into SAS as numeric and the other as character. I saved the spreadsheet as a tab-delimited file & imported that instead, but it's bugging me that I had this problem in the first place. Does anyone have any ideas what might have happened? The spreadsheet's from MS Excel 2010, and I am using SAS v9.2.
08-21-2012 09:57 AM
That is strange...have you tried find and replac[ing] the empty fileds with 0's and then PROC IMPORT?
08-21-2012 10:09 AM
That's an interesting idea, but I don't think that that's a solution for me with this data. 0 is a legitimate value for these data, and the blank cells indicate that data are not available for a given observation.
08-21-2012 10:24 AM
It that case, my guess is that you could possibly have character data on the spreadhseet...See if you can run the ANYALPHA() function on the columns to see what it's getting hung up on.
08-21-2012 10:51 AM
the cells with missing value are defined as text in excel. highlight the column then select clear format in excel you would see the cells with type=text. please check the attached file. I had the same problem a few days ago.
08-21-2012 11:18 AM
This is normal behavior for Excel. It is discussed every couple of months in SAS circles. See https://communities.sas.com/message/128876#128876.
Since your data is all numbers saving it to a CSV or other text format sounds like the best solution to me.
Remember that Excel is NOT a database system. In Excel each cell is independent.
08-21-2012 11:55 AM
The ANYCHAR and ANYSPACE functions have been helpful. Both of them are saying there is no hidden character data. I took another look in the excel spreadsheet, and it looks like even though it looks like the cell is blank, it isn't actually blank. The ISBLANK() function in excel is returning false (the two examples are running off of row 95). Apparently I need to talk to the person who sent me this file to find out how this file was generated - clearly there's something going on in the background.
Thanks for all the suggestions!