DATA Step, Macro, Functions and more

SAS Date/Balance Sum

Reply
Frequent Contributor
Posts: 140

SAS Date/Balance Sum

Hi,
 
I have a problem where SAS and Excel totals differ
 
Weighted_avg_term_date in EXCEL
Term_date * Total_Balance = X
02/04/2028 * 7582557.23 =  355206924974.97

Weighted_avg_term_date in SAS
Term_date * Total_Balance = Y
   02/04/2028 * 7582557.23 =  189027600722.29
 
Why don't they match when it's the same calculation, now im not sure what's right, SAS or Excel?
 
Term_Date is a Date variable.
Super User
Super User
Posts: 7,401

Re: SAS Date/Balance Sum

[ Edited ]

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.

Frequent Contributor
Posts: 140

Re: SAS Date/Balance Sum

This was the calculation they felt was correct. All I have been tasked with is replicating the numbers within SAS.

Super User
Super User
Posts: 7,401

Re: SAS Date/Balance Sum

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.

Super User
Posts: 6,936

Re: SAS Date/Balance Sum

You are multiplying a DATE with an AMOUNT? You're joking, right?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 140

Re: SAS Date/Balance Sum

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.

Super User
Posts: 6,936

Re: SAS Date/Balance Sum

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,500

Re: SAS Date/Balance Sum


KurtBremser 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.

Regular Contributor
Posts: 244

Re: SAS Date/Balance Sum

[ Edited ]

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.

SAS Employee
Posts: 285

Re: SAS Date/Balance Sum

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

Ask a Question
Discussion stats
  • 9 replies
  • 326 views
  • 4 likes
  • 6 in conversation