Please help me to figure out what is wrong with my Excel file

[Linlin]

Hi All,

The attached excel file has one date column. The last three rows (with date=11/26/2012) produce missing value after reading into sas (I use libname). I can't figure out why. Any help is highly appreciated!

[Reeza]

SAS is reading them in as numbers, or trying to at least. But if you look at the formula bar in excel they have a apostrophe (') before the date (probably because excel was trying to divide the numbers).

I think you can get rid of it by formatting the cells as text and then removing the apostrophe's and re-importing.

[Linlin]

Thank you Reeza! Is there a way to get rid of the '?

[PGStats]

The apostrophe is an old Excel convention that forces a string that looks like a number to remain a text value. The only way I know to get all the values is to use the MIXED=YES option on the LIBNAME statement. You will get a character-type column for your dates that you will have to transform using myDate = input(date, mmddyy10.).

PG

[Linlin]

Thank you PG!

I still got missing values after adding " mixed=yes" to the libname statement.

[PGStats · Accepted solution]

The problem is that Excel is only scanning 8 lines by default before determining the column type. You can change that behaviour, as recommended by SAS, by setting TypeGuessRows=0 in the Windows registry. Look at the MIXED= YES|NO paragraph in the documentation page :

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

PG

[Linlin]

Hi PG and Reesa,

Thank you for your help!:smileylaugh: