DATA Step, Macro, Functions and more

SAS reading in date as character 9, having trouble converting

Reply
Frequent Contributor
Posts: 82

SAS reading in date as character 9, having trouble converting

  So I am importing from excel a date variable which is 1/1/2011 format. SAS reads it in as a character 9 format. When I try to convert it, it always gives me errors. Ive tried doing input(date,f9.0), creating a new variable called newdate, formatting it as date9. (I want it as date9) and then saying newdate=date, etc.

Any ideas?

Super User
Super User
Posts: 6,500

Re: SAS reading in date as character 9, having trouble converting

You should use a date format for the INPUT() function.

newdate = input(date,mmddyy10.);

format newdate date9. ;

But 9 characters is not enough to store data in MM/DD/YYYY format.  If you have data with two digit month and two digit day you will lose the last character of the year.

Frequent Contributor
Posts: 82

Re: SAS reading in date as character 9, having trouble converting

Excel has it as 1/1/2001 and it never gets bigger than 9 because the day is always the 1st. It is interesting that it actually works for 2 of the files I have, with the excel formatting being the exact same, and from what I can see no inconsitincies in the actual data.

Super User
Super User
Posts: 6,500

Re: SAS reading in date as character 9, having trouble converting

Usually import from Excel will do this because there are missing values that make the cell look like character rather than number.  Part of the problem with using Excel as your data source.  Spreadsheets allow each cell to be independently typed, but SAS and databases require that columns (variables) be of a consistent variable type.

Super Contributor
Posts: 307

Re: SAS reading in date as character 9, having trouble converting

Are the errors showing up only on certain records, or the entire column?

Ask a Question
Discussion stats
  • 4 replies
  • 275 views
  • 0 likes
  • 3 in conversation