BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASbeginner20
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
PGStats
Opal | Level 21

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
SASbeginner20
Calcite | Level 5

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?

PGStats
Opal | Level 21

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
Kurt_Bremser
Super User

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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