turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Trouble with Adding a Format to a "Character" Date...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2014 08:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2014 10:49 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2014 10:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2014 10:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2014 10:49 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-25-2014 03:08 AM

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

Maxims of Maximally Efficient SAS Programmers