topic Re: Please help me to figure out what is wrong with my Excel file in SAS Programming https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97444#M20560 <HTML><HEAD></HEAD><BODY><P>Thank you Reeza! Is there a way to get rid of the '? </P></BODY></HTML> Mon, 04 Feb 2013 21:00:48 GMT Linlin 2013-02-04T21:00:48Z Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97442#M20558 <HTML><HEAD></HEAD><BODY><P>Hi All,</P><P>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!</P></BODY></HTML> Mon, 04 Feb 2013 20:42:12 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97442#M20558 Linlin 2013-02-04T20:42:12Z Re: Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97443#M20559 <HTML><HEAD></HEAD><BODY><P>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). </P><P></P><P>I think you can get rid of it by formatting the cells as text and then removing the apostrophe's and re-importing.</P></BODY></HTML> Mon, 04 Feb 2013 20:50:32 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97443#M20559 Reeza 2013-02-04T20:50:32Z Re: Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97444#M20560 <HTML><HEAD></HEAD><BODY><P>Thank you Reeza! Is there a way to get rid of the '? </P></BODY></HTML> Mon, 04 Feb 2013 21:00:48 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97444#M20560 Linlin 2013-02-04T21:00:48Z Re: Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97445#M20561 <HTML><HEAD></HEAD><BODY><P>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.).</P><P></P><P>PG</P></BODY></HTML> Mon, 04 Feb 2013 21:20:22 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97445#M20561 PGStats 2013-02-04T21:20:22Z Re: Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97446#M20562 <HTML><HEAD></HEAD><BODY><P>Thank you PG!</P><P>I still got missing values after adding " mixed=yes" to the libname statement.</P></BODY></HTML> Mon, 04 Feb 2013 21:28:03 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97446#M20562 Linlin 2013-02-04T21:28:03Z Re: Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97447#M20563 <HTML><HEAD></HEAD><BODY><P>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 :</P><P></P><P><A href="http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1acm90iijxn8j.htm" title="http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1acm90iijxn8j.htm">http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1acm90iijxn8j.htm</A></P><P></P><P>PG</P></BODY></HTML> Mon, 04 Feb 2013 21:59:15 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97447#M20563 PGStats 2013-02-04T21:59:15Z Re: Please help me to figure out what is wrong with my Excel file https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97448#M20564 <HTML><HEAD></HEAD><BODY><P>Hi PG and Reesa,</P><P>Thank you for your help!:smileylaugh:</P></BODY></HTML> Tue, 05 Feb 2013 15:06:44 GMT https://communities.sas.com/t5/SAS-Programming/Please-help-me-to-figure-out-what-is-wrong-with-my-Excel-file/m-p/97448#M20564 Linlin 2013-02-05T15:06:44Z