DATA Step, Macro, Functions and more

Trouble with Adding a Format to a "Character" Date Variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Trouble with Adding a Format to a "Character" Date Variable

Hello,

I imported a dataset from excel.

The date variable  looks like this in excel:

N/A
3/26/2003
3/31/2003
N/A
N/A
3/31/2003
3/26/2003
3/26/2003
3/31/2003
3/31/2003
4/2/2003
N/A
4/2/2003

I see from proc contents that this variable is a character variable with format and informat $7.  The values in SAS are now like "37711" I know i have the N/As there so it is definitely a character variable.

I would like to convert this variable to a numeric variable with a date format that is on the excel sheet eg "3/31/2003."

I really appreciate the help, thanks!


Accepted Solutions
Solution
‎06-24-2014 10:49 PM
Respected Advisor
Posts: 4,646

Re: Trouble with Adding a Format to a "Character" Date Variable

Ho! Then I guess it's mySASdate = input(ExcelDateStr, 7.) + '30DEC1899'd;

Seems like Excel considers 1900 to be a leap year, which it is not. :smileyangry:

See Excel 2000 incorrectly assumes that the year 1900 is a leap year

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,646

Re: Trouble with Adding a Format to a "Character" Date Variable

Try something like this:

data myData;

set ExcelData;

if anyalpha(ExcelDateStr) = 0 then

  mySASdate = input(ExcelDateStr, 7.) + '31DEC1899'd;

format mySASdate mmddyy10.;

run;

I can't test just now but it should be close. It relies on the fact that 01/01/1900 takes value 1 in Excel for Windows.

PG

PG
Occasional Contributor
Posts: 15

Re: Trouble with Adding a Format to a "Character" Date Variable

Thanks! However, it seems to be one day later than the correct date (should be 3/26/03 but is 3/27/03). How would I fix that?

Solution
‎06-24-2014 10:49 PM
Respected Advisor
Posts: 4,646

Re: Trouble with Adding a Format to a "Character" Date Variable

Ho! Then I guess it's mySASdate = input(ExcelDateStr, 7.) + '30DEC1899'd;

Seems like Excel considers 1900 to be a leap year, which it is not. :smileyangry:

See Excel 2000 incorrectly assumes that the year 1900 is a leap year

PG

PG
Super User
Posts: 6,936

Re: Trouble with Adding a Format to a "Character" Date Variable

That bug stems from Lotus 1-2-3, and the incompetents in Redmond simply copied it and have still not gotten around to fix it. One of the reasons I consider their company name an accurate description of their brains.

Hint: they just need to adjust their weekday function for dates before March 1, 1900 and officially set their zero date to December 30, 1899, as you did. Every existing Excel sheet without dates before March 1, 1900 would be unaffected, and it would open the path for Excel to deal with negative date values. As an aside, it would render the correct number of days if one made the calculation "today - 01/01/1900".

Their "reasons" in Excel 2000 incorrectly assumes that the year 1900 is a leap year are of course just "we do not want to think this through, because thinking hurts us too much".

Double Hint: OpenOffice and LibreOffice, among others, do not have this problem; they assume day zero to be '30dec1899'd, do not consider 1900 a leap year, and they work with negative date values.

PS. To make the joke even funnier: MS Access uses the same internal date values and starts at 12/30/1899, making kb214326 even more ridiculous!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 266 views
  • 7 likes
  • 3 in conversation