https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28299#M5204 Hi,<BR /> This is a nagging problem when converting excel data to SAS datasets especially with the date fields.<BR /> I have this field ship_date where some values are of fomat 02/21/2007 and some are of 2/2/2007.<BR /> As first step, I create a new column in excel convert all ship_date values to character and then import to SAS. Even then there are some values which wouldnt convert to the expected in excel either.2/22/2007 remains as is so I have to add a zero before month and year and then do the data conversion.<BR /> This will take up long time to clean/modfiy formats. <BR /> I'm wondering if there is any other technique that will help reduce this manual intervention? Wed, 06 May 2009 17:51:50 GMT SASPhile 2009-05-06T17:51:50Z https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28299#M5204 Hi,<BR /> This is a nagging problem when converting excel data to SAS datasets especially with the date fields.<BR /> I have this field ship_date where some values are of fomat 02/21/2007 and some are of 2/2/2007.<BR /> As first step, I create a new column in excel convert all ship_date values to character and then import to SAS. Even then there are some values which wouldnt convert to the expected in excel either.2/22/2007 remains as is so I have to add a zero before month and year and then do the data conversion.<BR /> This will take up long time to clean/modfiy formats. <BR /> I'm wondering if there is any other technique that will help reduce this manual intervention? Wed, 06 May 2009 17:51:50 GMT https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28299#M5204 SASPhile 2009-05-06T17:51:50Z https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28300#M5205 Try using INFORMAT ANYDTDTE. for your input conversion.<BR /> <BR /> Scott Barry<BR /> SBBWorks, Inc. Wed, 06 May 2009 17:55:18 GMT https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28300#M5205 sbb 2009-05-06T17:55:18Z https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28301#M5206 I'm using import wizard Wed, 06 May 2009 18:07:43 GMT https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28301#M5206 SASPhile 2009-05-06T18:07:43Z https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28302#M5207 There is a fair chance that the 2/2/2007 date in excel is actually entered in the cell as <SPACE>2/2/2007 and that excel, in it's fashion is just storing text in a column meant for a date. It looks like a date, but it is not.</SPACE> Thu, 07 May 2009 01:08:31 GMT https://communities.sas.com/t5/SAS-Programming/Excel-to-SAS/m-p/28302#M5207 Doc_Duke 2009-05-07T01:08:31Z