Help using Base SAS procedures

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

Reply
Contributor
Posts: 22

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

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!

Contributor
Posts: 72

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

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

Contributor
Posts: 22

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

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!

Contributor
Posts: 72

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

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.

Contributor
Posts: 72

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

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

Super Contributor
Posts: 1,636

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

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.


character.png
Super User
Super User
Posts: 6,499

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

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.

Contributor
Posts: 22

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

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!
excel - not blank.png

Ask a Question
Discussion stats
  • 7 replies
  • 7254 views
  • 3 likes
  • 4 in conversation