topic SAS Date/Balance Sum in SAS Programming

SAS Date/Balance Sum

anonymous_user — Wed, 31 May 2017 09:45:13 GMT

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.

Re: SAS Date/Balance Sum

RW9 — Wed, 31 May 2017 09:55:29 GMT

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.

Re: SAS Date/Balance Sum

anonymous_user — Wed, 31 May 2017 09:54:32 GMT

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

Re: SAS Date/Balance Sum

RW9 — Wed, 31 May 2017 09:59:37 GMT

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.

Re: SAS Date/Balance Sum

Kurt_Bremser — Wed, 31 May 2017 10:13:31 GMT

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

Re: SAS Date/Balance Sum

anonymous_user — Wed, 31 May 2017 10:15:47 GMT

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.

Re: SAS Date/Balance Sum

Kurt_Bremser — Wed, 31 May 2017 11:04:29 GMT

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)

Re: SAS Date/Balance Sum

ballardw — Wed, 31 May 2017 14:57:54 GMT

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

Re: SAS Date/Balance Sum

snoopy369 — Wed, 31 May 2017 15:14:51 GMT

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.

Re: SAS Date/Balance Sum

Vince_SAS — Wed, 31 May 2017 17:30:27 GMT

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