DATA Step, Macro, Functions and more

Numeric days to a date format

Accepted Solution Solved
Reply
Super Contributor
Posts: 357
Accepted Solution

Numeric days to a date format

I'd like to do a quick check If I'm missing something obvious here? Output from below is a future date 09/02/2076. Any idea why?

 

data p.a;

a=42614;

format a mmddyy10.;

run;

 

Thanks a lot.


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,211

Re: Numeric days to a date format

[ Edited ]

Oh. This looks like data imported from MS Excel. If somehow the fact that a date is concerned gets missed during the import from Excel to SAS, raw Excel dates will look like this, as Excel counts days from 1899-12-31. See this for reference:

data _null_;
x1 = 44227;
diff = '01jan1960'd - '30dec1899'd;
x1 = x1 - diff;
format x1 yymmddd10.;
put x1=;
run;

 

Edit: corrected Excel basedate to 1899-12-30 (1960-01-01 in SAS is 0, 1900-01-01 in Excel is 1, but because of the "leapyear 1900" bug in Excel it actually has to be 2)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Valued Guide
Posts: 563

Re: Numeric days to a date format

From the documentation:

"A SAS date value is a value that represents the number of days between January 1, 1960, and a specified date. SAS can perform calculations on dates ranging from A.D. 1582 to A.D. 19,900. Dates before January 1, 1960, are negative numbers; dates after are positive numbers."

Super User
Posts: 10,211

Re: Numeric days to a date format

A date value in SAS is the count of days starting at 1960-01-01 (days before that have a negative value). If you count 42614 days from that, you arrive at that date in 2076. Today's date is 21360 as a raw numerical value:

data _null_;
x1 = today();
put x1=;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 357

Re: Numeric days to a date format

Yes, sorry for making my question unclear. I'm surprised because these are supposed to be dates for the start date of clinical trials in the past. I'm not supposed to have future dates. And I wonder how come?

date.png

Solution
3 weeks ago
Super User
Posts: 10,211

Re: Numeric days to a date format

[ Edited ]

Oh. This looks like data imported from MS Excel. If somehow the fact that a date is concerned gets missed during the import from Excel to SAS, raw Excel dates will look like this, as Excel counts days from 1899-12-31. See this for reference:

data _null_;
x1 = 44227;
diff = '01jan1960'd - '30dec1899'd;
x1 = x1 - diff;
format x1 yymmddd10.;
put x1=;
run;

 

Edit: corrected Excel basedate to 1899-12-30 (1960-01-01 in SAS is 0, 1900-01-01 in Excel is 1, but because of the "leapyear 1900" bug in Excel it actually has to be 2)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 73 views
  • 1 like
  • 3 in conversation