Dates are stored differently in SAS and Excel. SAS dates are number of days since January 1, 1960. Excel has a 1900 (I think from memory) start date, hence why you have different results. The real question here is why you are multiplying a Date - i.e. not a time period or number of days etc. - but a numeric. I think you have your formula wrong there, maybe you need number of days * value or something.
E.g. in SAS 02/04/2008 is mmddyy8., = 21949.
This was the calculation they felt was correct. All I have been tasked with is replicating the numbers within SAS.
You will need to point out the incorrectness to "them" then.
Assuming the date is 04Feb2028:
SAS:
21949 * 7582557.23
Excel:
46787 * 7582557.23
This is why you get the different results, the base interpretation of what a date is differs between the two systems, and is also the prime reason why this calculation makes no sense whatsoever. If you move it to another system, then the number will change again. The calculation needs to be based on some fixed data, not an adhoc representation of how a system calculates dates.
You are multiplying a DATE with an AMOUNT? You're joking, right?
Nope but that isn't for me to get invloved with, nothing to do with me! All I have been asked to do is replicate what they have calculated within excel whether or not I agree with it.
First of all, this is so utterly, incredibly stupid that my head hurts just thinking about it.
I just tested this "calculation" with Excel 2010, and - lo and behold! - I get a different result already:
04.02.2028 | 7582557,23 | 354765105120,01 |
02.04.2028 | 7582557,23 | 355204893439,35 |
Note that I tested your date as DDMMYY or MMDDYY
Are you sure that you are showing the complete Excel calculation in your initial post?
The same result is achieved with LibreOffice.
Now, if we take care of the date offset inherent in Excel:
02.04.2028 | 01.01.1900 | 7582557,23 | 355197310882,12 |
(subtracting column B from A and using the result in the multiplication)
and do the same in SAS:
data _null_;
val1 = ('02apr2028'd - '31dec1899'd) * 7582557.23;
put val1= 20.2;
run;
we get this:
24 data _null_; 25 val1 = ('02apr2028'd - '31dec1899'd) * 7582557.23; 26 put val1= 20.2; 27 run; val1=355197310882.12
Tadaa!
(Note that one has to use 31dec1899 because of a serious date calculation bug in Excel, which thinks that 1900 was a leap year)
@Kurt_Bremser wrote:
(Note that one has to use 31dec1899 because of a serious date calculation bug in Excel, which thinks that 1900 was a leap year)
AND Excel thinks 0 Jan 1900 is a valid date.
I hope you at least bring up the issue here (that this doesn't really make sense). If you worked for me and didn't, I'd consider it a major negative in your review. Of course it may well be for someone else to say that it does make sense, but that's up to them once you point it out. Simply hiding behind 'it's not my job' is not a sign of a good employee.
Of course if they're evaluating the change in (whatever) since 1/1/1900, then this is fine. But that's an important detail, don't you think? And if that's what they're doing, then Kurt's answer is the correct one. In fact you might ask whether they want the actual change since 1/1/1900, or the Excel-incorrect value, which will be slightly different - again as Kurt points out, Excel incorrectly considers 2/29/1900 a valid date, so it counts one more day in the period 1/1/1900->today than it should.
It may well be that replicating the bug is desired - that replicability is the key here and not accuracy - but I'd definitely want that documented as a specific requirement (the 2/29/1900 day being included) so that in 5 years when my successor asks why we're subtracting 12/31/1899 the documentation explains why - and who made the decision.
For a humorous but informative view of Excel dates:
My First BillG Review
https://www.joelonsoftware.com/2006/06/16/my-first-billg-review
Vince DelGobbo
SAS R&D
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.