DATA Step, Macro, Functions and more

Excel to SAS

Reply
Super Contributor
Posts: 647

Excel to SAS

Hi,
This is a nagging problem when converting excel data to SAS datasets especially with the date fields.
I have this field ship_date where some values are of fomat 02/21/2007 and some are of 2/2/2007.
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.
This will take up long time to clean/modfiy formats.
I'm wondering if there is any other technique that will help reduce this manual intervention?
Super Contributor
Super Contributor
Posts: 3,174

Re: Excel to SAS

Try using INFORMAT ANYDTDTE. for your input conversion.

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 647

Re: Excel to SAS

I'm using import wizard
Trusted Advisor
Posts: 2,113

Re: Excel to SAS

There is a fair chance that the 2/2/2007 date in excel is actually entered in the cell as 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.
Ask a Question
Discussion stats
  • 3 replies
  • 125 views
  • 0 likes
  • 3 in conversation