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!
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
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
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?
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
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.