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!
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 :
PG
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.
Thank you Reeza! Is there a way to get rid of the '?
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
Thank you PG!
I still got missing values after adding " mixed=yes" to the libname statement.
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 :
PG
Hi PG and Reesa,
Thank you for your help!:smileylaugh:
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.