PROC IMPORT: Excel spreadsheet is numeric, but reading in as character?

[rpaulus]

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.

Thanks!

[robby_beum]

That is strange...have you tried find and replac[ing] the empty fileds with 0's and then PROC IMPORT?

[rpaulus]

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.

Thanks anyway!

[robby_beum]

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.

[robby_beum]

Other options to search for are ANYPUNCT() for punctuation and ANYSPACE() for white space...

[Linlin]

Hi,

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.

[Tom]

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.

[rpaulus]

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!

[wilson]

Here is a simple trick to avoid this problem.

Usually when importing data from Excel or csv,  SAS decide whether variable type should be numeric or character  while reading the first few rows of the dataset.

 As a trick, you can insert a new row into your input Excel file after the label row and assign value 9999 for all numeric columns   and 'aaaa' for all character columns and then import into SAS. After you import delete this additional row from the dataset.

Good luck for your dataset!