BookmarkSubscribeRSS Feed
rpaulus
Obsidian | Level 7

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!

8 REPLIES 8
robby_beum
Quartz | Level 8

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

rpaulus
Obsidian | Level 7

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
Quartz | Level 8

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
Quartz | Level 8

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

Linlin
Lapis Lazuli | Level 10

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
Tom
Super User Tom
Super User

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
Obsidian | Level 7

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

wilson
Fluorite | Level 6

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! 

 

 

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 21879 views
  • 6 likes
  • 5 in conversation